DAX PowerBI: Replaced blank values with zero and issue with chart

3
votes

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

I decided to change Blank() values with zero adding a 0 at the end of calculation.

It works great, however I have an issue with limitaton date hierarchy, because now my chart contains a lot of months without value (first value begins in 2017, date hierarchy first value begins in 2000).

Test:=
  CALCULATE (
    SUM( quantity ),
    flag = 1, 
    title = "WEEKS"
   ) + 0
1
powerbidax
Have you tried adding a filter on date to your visual? – Jelle Hoekstra
Yes, I tried add filter in report and I tried add filter in measure- I still see all periods. – kittysmile
Can you share a screenshot of the situation? I don't think that should be happening. – Jelle Hoekstra
It can happen when you have 2 tables, your data table and your value table. Have you considered using an IF statement? – Aldert
Why not just remove rows from your date dimension before 2017? – greggyb

1 Answers

1
votes

Instead of a plain 0, you could add an IF to specify to only add that after the first value. Something like this:

Test:=
VAR FirstDate = CALCULATE ( MIN ( date ), ALL( Dates ), quantity > 0 )
RETURN
  CALCULATE (
    SUM( quantity ),
    flag = 1, 
    title = "WEEKS"
   ) + IF( date > FirstDate, 0 )

If the condition is false, the IF returns a blank and it shouldn't show up.