Forum Discussion
Show zero on Line chart where no data for date
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:
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. 🙂
Sorry, PaulDBrown , I feel like I am being a terrible pain, but now I am back to where I started:
a gap in the Blue shift line and chart stops at March 23rd - last date for data in the Audit table.
Community ChampionAnonymous
No worries, it's my fault for trying to cut corners.
So... Keep your original measure:
AuditCount = COUNT(Audits[AuditID])+0And 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)
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
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:
PaulDBrown , so from my report, I have this measure:
+ This measure:
and my chart looks like this.... no change from above....😶
Hi PaulDBrown ... apologies for the delay in getting back to you... was getting pulled away from this project for a couple of days. Now that I am back at it, I have tried your method, above and am getting better results....
A few responses back you had told me to change the X-Axis to the Date field in the Calendar table.... but here's my dilemma... we don't work most weekends so I am getting Zero results for weekend dates because I have no data for these dates. Is there a work-around for that?
Here is my data in a matrix:
So in the line chart visual, I want to show Zero for B shift on March 16, but I do not want to show any data for March 20 & 21 as we were not working... same for all other weekend dates in March. But if I switch to show February, we did work on Feb 27, so would want to show that data in the line chart visual. Does that make sense?