Show months on "Line and clustered column chart" even when there is no data

[Masikonde]

Hi All,

Periodically security training are send out to people, I need each month to appear on the "Line and clustered column chart" even when there is no data, I'm currently struggling to have September 2019 show in the graph. Has anyone dealt with this in the past? Below are the details

[Masikonde · Accepted solution]

Thank you everyone for all your help, I was able to use your inputs to fix the problem, I also discovered that you can select the the month or dates on the data , and apply the filters as shown in below to get all the months within 12 calendar months or any other period of interest.

[Icey · Accepted solution]

Hi @Masikonde ,

Glad to hear that. Please accept your solution above so that people who may have the same question can get the solution directly.

Best Regards,

Icey

[Masikonde · Accepted solution]

Thank you everyone for all your help, I was able to use your inputs to fix the problem, I also discovered that you can select the the month or dates on the data , and apply the filters as shown in below to get all the months within 12 calendar months or any other period of interest.

[kentyler]

This is a case where adding a "calendar table" is really helpful. If you had a separate table with all the months/dates then you could link it to your table and use the months from the calendar table in your graph. That way they would show up even when there was not data for those months.

Here is a link to a post with more information.https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[parry2k]

@Masikonde assuming you have date dimension in you model that has relationship with your transaction table. If you don't have date dimension, as a best practice, you should add one. There are many posts on how to create one using DAX or Power Query.

Once you have date dimension in your model, use that on X-AXIS and on drop down in X-AXIS column, choose Show Items wiht no data and that will do it.