How to show missing dates on PowerBI Clustered Column Chart

Question

I have a data set which has incidents resolved w.r.t date. I would like to look at the trends of number of incidents resolved over the past 45 days only on a Clustered column chart.

I couldn't see entries for specific dates (because the number of incidents resolved was zero on that day). How do I include those dates as well showing the number to be zero?

I tried the following:

1) Enabling "Show items with no data" - While this working fine, it is removing the last 45 days filter and is showing me unnecessary trends. The last 45 days filter is set on a page level and report level filter as well.

2) Creating a new measure to replace null values with 0 when the count of incidents resolved on a particular day is 0 - It again removed last 45 days filter.

Someone please let me know what I can do to get the required trends over last 45 days.

Thanks

Answer 1

This can be late reply, but I hope it may be useful for future users. As I suggested in a comment, you can try using measure for achieving this.

I have designed the following table with sample data.

INCIDENT_RECORD Table:

Created Measure:

Total Incident = IF(SUM(INCIDENT_RECORD[Resolved Incident])=BLANK(),0,SUM(INCIDENT_RECORD[Resolved Incident]))

Now I have designed Clustered Column Chart with & without Measure to show difference for you.

Clustered Column Chart with/without Measure:

• First graph is showing label as 0 (Zero) which doesn't have any value for that date.
• Second graph is not showing data for Jan-03 in your case.

Feel free to ask your doubts/clarifications in the comment section.

Answer 2

First, make sure the column on your x-axis is a date data type. Then go to the Format tab for the visual and under the X-Axis options, set the Type to Continuous (rather than Categorical).