Show zero values in chart for Running Last 12 months actuals

[Anonymous]

Hi,

I'm having an issue with a "line & stacked column chart" in which I'm showing, for the last 12 months, the number of complaints and the complaints per MT. As long as there are complaints for a specific month in the dataset, the visual is showing all the months. However, when there's no complaint for a month (hence also no complaint per MT, the graph is simply skipping that month. Is there a way to show the months with no values? 

Sample which is working when there's a complaint for all months:

Below the sample when a site is selected which does not have complaints for every month in the last 12 months.

What I need is for this graph to show the "missing" months , basically as a flat column and line

This is the DAX I'm using to calculate the last 12 months actuals, using a disconnected date table:

Complaints Last 12 months =

VAR CurrentDate = MAX ( 'Calendar'[Date] )

VAR PreviousDate = EDATE( CurrentDate ; -12 )

VAR Result =

CALCULATE( [Total Complaints] ;

FILTER( DimDateTable ; DimDateTable[Date] >= PreviousDate && DimDateTable[Date] <= CurrentDate ))

RETURN

Result

I've tried adjusting the part after RETURN to the following, but then the visual is showing ALL months again which is what I don't want:

RETURN

CALCULATE( IF( Result = BLANK() ; 0 ; Result) )

Any help on how to fix is highly appreciated.

[Anonymous]

Please any luck with this. @Anonymous Please can you send solution here or if I can send a dm. i need it for the project I am currently working on

[suryaburaboyina]

Hi,

please can you share any sample data set, it will be easy to trace the issue.

Thanks,

Surya

[MattAllington]

Click on the chart

go to the fields card on the right

find the item on the axis (month)

click the drop down arrow (or is it right click, or is it an ellipsis?)

select “show items with no values”

---

---

@Anonymous wrote:

Hi,

I'm having an issue with a "line & stacked column chart" in which I'm showing, for the last 12 months, the number of complaints and the complaints per MT. As long as there are complaints for a specific month in the dataset, the visual is showing all the months. However, when there's no complaint for a month (hence also no complaint per MT, the graph is simply skipping that month. Is there a way to show the months with no values? 

Sample which is working when there's a complaint for all months:

Below the sample when a site is selected which does not have complaints for every month in the last 12 months.

What I need is for this graph to show the "missing" months , basically as a flat column and line

This is the DAX I'm using to calculate the last 12 months actuals, using a disconnected date table:

Complaints Last 12 months =

VAR CurrentDate = MAX ( 'Calendar'[Date] )

VAR PreviousDate = EDATE( CurrentDate ; -12 )

VAR Result =

CALCULATE( [Total Complaints] ;

FILTER( DimDateTable ; DimDateTable[Date] >= PreviousDate && DimDateTable[Date] <= CurrentDate ))

RETURN

Result

I've tried adjusting the part after RETURN to the following, but then the visual is showing ALL months again which is what I don't want:

RETURN

CALCULATE( IF( Result = BLANK() ; 0 ; Result) )

Any help on how to fix is highly appreciated.

---

[Anonymous]

Thanks, but I already tried this. Doing so will also display ALL months instead of just the last 12 months...

[daltonsa]

Hello Bthurlings,

Did you ever resolve this issue?

I have the same issue and was wondering how you did.

Thanks 

[Anonymous]

Yes indeed I managed to resolve.  I used the initial measure

IF ( ISBLANK ( Result ) , 0 , Result )
and combined that with logic applied to the date table (a column which indicates if a date is 12 months in the past) which I use as a filter on a viz or report page. Not sure if it's the most optimal but it's flexible.

[daltonsa]

Hello Bthurlings,

Will you be so kind to give a step by step? 

I created the measure IF ( ISBLANK ( Result ) , 0 , Result ) like you said and put the Calender[Date] filed as the X-axis and also put the Calender]Date] as the Filter in the Filter all pages filter panel and filtered by last 6 Calender months.

That did is still showing the other months as well.

What is the logic that you cobined with the Date table that you mentioned?

Thanks 

[Anonymous]

PM me and I'll share a sample pbix with you (I can't seem to find the option to publish that here)

[daltonsa]

Hey bthurlings,

I just sent you a Private Message