Show zero on Line chart where no data for date

[Opal55]

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 cannot get the chart to display the way I need.   I am tracking audits performed by shift.  On 03/16/21, the "Orange" shift did 1 audit and the "Blue" shift did none.  I do not want to see a gap in the line for the "Blue" shift, but rather have it drop down to zero on the X- Axis.

The chart should look something like this:

I am using the following as my Measure to calculate the Audit Count:

AuditCount = COUNT(Audits[AuditID])+0

My X-Axis is Categorical as I want to display all dates.  Changing to Continuous does not show the desired results as the Blue shift data line jumps from 03/15/21 to 03/17/21 and I do not want that.

Any suggestions / support to resolve this would be appreciated.  Thank you.

[PaulDBrown · Accepted solution]

@Opal55 

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:

[Syndicate_Admin]

Hello, good afternoon
I have a case similar to the problem posed, I include zeros where there are no records, but I need it to show me only the projects in which the employee has participated, not all the projects I have in the data model.
How could I do it?
Since it shows me all the projects, but it does not respect the employee filter.
as I can attach pbix, so that you can help me.

Regards, Thank you very much.

[PaulDBrown]

@Opal55 

Are you using a Date Table covering the range of dates in your model and with continuous dates?

[Opal55]

yes, I have a date table:

Calendar = CALENDARAUTO()

[PaulDBrown]

2 here is what I get using the date table for the x-axis and setting the X axis to categorical

The top chart shows a simple SUM; the bottom shows SUM + 0

[Opal55]

@PaulDBrown :  Unfortunately, that is not what I get.  I am counting records, not summing them, as the data table shows the date of the audit, the area audited, the employee audited and the shift as well as an audit ID field.  I am counting the Audit ID field as that is what I showed in my original post.  Any other thoughts?

[PaulDBrown]

@Opal55 

Try either:

AuditCount = 
VAR _count = COUNT(Audits[AuditID])
RETURN
_count + 0

or 

AuditCount = COUNT(Audits[AuditID])

and then 

AuditCount = [AuditCount] +0

[Opal55]

Option 1 does not work... will try option 2, tomorrow.... I presume these are two separate measures each with a unique name?

[PaulDBrown]

@Opal55 

Correct, they are sepearate measures (sorry about repeating the name)

[Opal55]

Sorry, @PaulDBrown , option 2 does not work either.... I still have a break in the line.  I wish I could share the database, but I cannot do it from work.  I have stripped it down and made a sample and tried both measures and get the following results in my stripped down version:

[PaulDBrown]

@Opal55 

I don't know what the problem is at your end. Using a simple data sample I get this for

Count = COUNT('Table'[Value])

Measure = COUNT

And for this: 

Count + 0 = COUNT('Table'[Value]) + 0

I get this:

Measure = COUNT + 0

And to compare them:

Both have the x-axis set as categorical and the date is from the date table

[Opal55]

Hi! @PaulDBrown I am stumped as well.... from everything I read yesterday and what we have discussed in this thread it should work... I am stumped as well.  In my sample file, I have the stripped down data in 4 columns, AuditID, Date, JobID and Shift.  Does that make sense?

[PaulDBrown]

@Opal55 
I can't see how the number of columns would affect the result; after all, you are counting IDs (from a single cloumn).
However, I see from one of the visuals that the x-axis is from "ShiftDate". Is that your Date Table?

Here is the model from my example. The x-axis is the Date field from the Date Table:

 The Date Table has conitnuous dates covering the range of dates in the model (so including the dates where there are no values in the Fact Table)

[Opal55]

There are two tables like this:

[PaulDBrown]

@Opal55 

Ok, but it seems you are using the ShiftDate field (from the 'Audits' table) as your axis instead of the date field from the Calendar table:

Change the field on the x-axis to the date field from the Calendar table

[Opal55]

Okay, @PaulDBrown , below is my result.... but I don't want to show data for dates that have not happened yet:

I would like the lines to stop at at the last date where there is data in the Audits table, but still show all the dates of the current month....is that possible?

[PaulDBrown]

@Opal55 

Sure, just use the following measure instead of the COUNT + 0 measure:

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

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

[Opal55]

@PaulDBrown  Getting a "Parameter is not the correct type" error?

[PaulDBrown]

@Opal55 
That's because "Calendar" is a DAX function per se, so you need to specify that you are pointing at an actual table by using the proper table syntax of 'Calendar'...

Try:

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

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

If not, change the Calendar table name to 'Calendar Table' and adjust the syntax in the measure to the new name

[Opal55]

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.

[PaulDBrown]

@Opal55 

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)