Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Show zero on Line chart where no data for date

Hello all.  I am trying to build a line chart visual that will display Zero when no data is present.  I have been reading through the forum for some time and have tried multiple ways suggested and ca...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    Anonymous 

    Ok, here is a true and tested way:

    Create a measure along the lines of:

    Cutoff = 
VAR _MaxDataDate = CALCULATE(MAX('Audits'[ShiftDate]), ALL('Audits'))
RETURN
IF(MAX('Calendar'[Date]) <= _MaxDataDate, 1)

    Now select the visual and add this measure to the "Filters on this visual" in the filter pane and set the value to 1. 

    Use the proven [AuditCount] measure in the visual:

     

     

  • Anonymous's avatar
    Anonymous
    5 years ago

    PaulDBrown as a follow-up to my reply yesterday, I came up with a different solution.  As well as yours worked, it did not work for what I need to show.  What I ended up doing is adding two calculated columns to my Audit Table.

    CountO = If(Audits[Shift]="O",1,0)
CountB = If(Audits[Shift]="B",1,0)

    and added them to my visual.  I got the chart and the matrix to display with zeros as I wanted and no unwanted dates.

    Thank you for putting up with me.  🙂

     

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
5 years ago

Anonymous 

No worries, it's my fault for trying to cut corners.

So... Keep your original measure:

AuditCount = COUNT(Audits[AuditID])+0

And then create the new one using:

New measure =
VAR _Count = COUNT(Audits[AuditID])
VAR _Lastdate =
    CALCULATE (
        MAX ( Calendar[Date] ),
        FILTER ( 'Calendar', NOT ( ISBLANK ( _Count ) ) )

    )
RETURN
    IF ( MAX ( Calendar[Date] ) <= _LastDate, [AuditCount] )

 

This measure will include data up to the last date for data in the Audit table. If you want the cutoff at a different date, we need to change the _Lastdate variable accordingly (if so, let me know where you want the cutoff date)

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

PaulDBrown , so from my report, I have this measure:

+ This measure:

and my chart looks like this....  no change from above....😶