How to show missing dates on PowerBI Clustered Column Chart

4
votes

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?

Clustered Column Chart

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

2
visualizationdata-visualizationpowerbipowerbi-desktop
You can try creating measure to SUM daily incident field, and if you found blank can store it as zero. Don't forget to enable label, now you can get zero figure in chart for the date which don't have any incident resolved count. Good Luck... – Wanna Coffee
See my answer which i have updated to this question with above-mentioned workaround... – Wanna Coffee

2 Answers

1
votes

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:

enter image description here

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:

enter image description here

  • 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.

0
votes

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).

X-Axis Settings