Get categorical type axis to display entries without data on a column chart

[Anonymous]

Hi all, 

I tried to show a daily trend of sales by using column chart consisting of the date axis (has to be categorical for format purposes) and values, however, entries without items don't show despite using the "Show items with no data" option. Is there any way to get all values displayed even for days without sales? 

It frankly looks like that: 

[Anonymous]

@Anonymous - You'll need to:

1. Create a Date table. It could be as simple as the following DAX, but generally, more attributes will be helpful.

Date = CALENDAR(DATE(2019,1,1), DATE(2019,12,31))

1. Create a relationship between the date table and your table
2. Use the date from the date table on the axis.

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[Anonymous]

Thanks @Anonymous, 

I forgot to say in the initial post that I have already created a dates table using exactly the same DAX command but I still get the same problem - dates without sales still don't display. 

 

[Anonymous]

@Anonymous  - I think it should work, as long as the axis is using the date from the date table. Could you share a screenshot that shows the various columns in use?

[Anonymous]

Hi @Anonymous, 

The dates column on the table I created (format is Date type and Customised to display as d mmm) is here: 

 
This is connected to a corresponding column from my main dataset with the same format:

As mentioned in the previous post, on the report mode, the x axis in the chart is shown as categorical (so I can keep the format) and the 'Show  Items without data' option is ticked. 

Not sure if that's sufficient, but I'm happy to provide further info if necessary.

[Anonymous · Accepted solution]

@Anonymous  - The relationship between the 2 tables is Bi-directional. Please change the relationship so that Programmes does not filter Dates and let us know whether that fixes the issue.