CODEWITHSUNDEEP
powerbidax
kittysmile
kittysmile

Reputation: 51

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

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

Upvotes: 3

Views: 725

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40264

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.

Upvotes: 1

Related Questions