DAX Power BI Replaced blank values with zero and issue with chart

0 votes

When sales is blank, I want to see month with 0 value. I tried to make a report in Power BI with sales month by month for the last 20 months, however when sales is blank, I want to see month with 0 value.

I chose to replace all Blank() values with 0 and add a 0 at the conclusion of the calculation.

It works fine, however I'm having trouble with the date hierarchy limitation because my chart now has a lot of months with no value (first value begins in 2017, date hierarchy first value begins in 2000).

Test:=
  CALCULATE (
    SUM( quantity ),
    flag = 1, 
    title = "WEEKS"
   ) + 0
Feb 23, 2022 in Power BI by surbhi
• 3,810 points
1,035 views

1 answer to this question.

0 votes

It seems like you're facing an issue with the date hierarchy in Power BI when you're replacing blank values with 0 in your calculations. The date hierarchy is extending beyond the actual data range, causing your chart to display months with no value. To resolve this issue, you can try the following steps:

  1. Modify Data Source: Ensure that your data source includes data for all the months you want to display in your chart. If your data only goes back to 2017, but you want to show months from 2000, you might need to create or generate additional data points for the missing months.

  2. Create a Calendar Table: Consider creating a separate calendar table in Power BI that spans the entire date range you want to display, from 2000 to the present. This calendar table can be linked to your main data table using a relationship based on the date. This will ensure that all months are represented in the date hierarchy.

  3. Use the Calendar Table in Calculations: Instead of relying on the date hierarchy of your main data table, use the date column from your calendar table in your calculations. This will allow you to create consistent calculations across all months, including those with no data.

Here's an example of how you might modify your calculation:

​Test := VAR SelectedDate = MAX('Calendar'[Date]) RETURN CALCULATE ( SUM( quantity ), flag = 1, title = "WEEKS", 'Calendar'[Date] = SelectedDate ) + 0

In this example, 'Calendar' is the name of the calendar table you've created, and 'Date' is the column representing the dates.

By using the calendar table, you can ensure that your calculations work consistently across all months, regardless of whether there's data for each individual month. This should help you achieve your goal of displaying months with 0 value when sales are blank.

Empower Your Data Insights with Power BI Certification.

answered Aug 22, 2023 by anonymous
• 1,180 points

Related Questions In Power BI

0 votes
1 answer

Chart with only some values in field in a graph in power BI?

Hi Banu, While creating a chart, put the ...READ MORE

answered Apr 10, 2019 in Power BI by Cherukuri
• 33,030 points
676 views
0 votes
2 answers

Power BI Dax Multiple IF AND Statements

HI I have Column Patient 12 13 14 15 18 IN Patient=12, 13 Out ...READ MORE

answered May 23, 2019 in Power BI by VNK
18,349 views
+1 vote
1 answer

Have column with multiple values in powerBI, for use with a slicer in power bi

It can be achieved depending on the result you ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 18,570 points
6,910 views
0 votes
1 answer

list reports with calculated percentage in Power BI using dax

Create a Measure called Total Revenue: Total Revenue = SUMX( ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 18,570 points
1,319 views
0 votes
1 answer

Displaying Table Schema using Power BI with Azure IoT Hub

Answering your first question, Event Hubs are ...READ MORE

answered Aug 1, 2018 in IoT (Internet of Things) by nirvana
• 3,130 points
956 views
+1 vote
1 answer

Unable to install connector for Power Bi and PostgreSQL

I think the problem is not at ...READ MORE

answered Aug 22, 2018 in Power BI by nirvana
• 3,130 points
2,407 views
+2 votes
2 answers

Migrate power bi collection to power bi embedded

I agree with Kalgi, this method is ...READ MORE

answered Oct 11, 2018 in Power BI by Hannah
• 18,570 points
1,079 views
+1 vote
1 answer

Connect power bi desktop to dataset and create custom reports

Yes using Power BI REST API to ...READ MORE

answered Sep 18, 2018 in Power BI by Kalgi
• 52,360 points
1,390 views
0 votes
1 answer

How to Split Columns in Power BI?

Splitting columns in Power BI using the ...READ MORE

answered Jan 6 in Power BI by anonymous
• 1,180 points
546 views
0 votes
1 answer

Calculate average speed in Powerbi using DAX

To calculate the average speed for automobiles ...READ MORE

answered Jun 20, 2023 in Power BI by Neelam
• 1,180 points
887 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP