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

Question

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

Answer 1

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.