Showing Items with No Data on a Date Axis

[bricwood]

I have a report that shows 13 months worth of data. The current month updates as information becomes available.

Unfortunately we have a number of months where there was no data so I use the "Show items with no data" on the Date. This then causes the 13 month Relative date filtering to be completely ignored and it shows all months of 2018 and 2019.

I have tried using a Date Table but that removes the empty dates when the relationship is created..

My short term solution has been to create an Excel sheet that has dates and no values for the months I want, but I have to update it every month. Obviously I want to automate these as best as I can.

Any assistance would be greatly appreciated.

[Nishantjain]

Hi there

What is the measure you are using in the visual?

I would suggest you try the following

If(isblank([your measure]), 0,[your measure])

This should show all the dates that you have

[bricwood]

Thanks for the response.

I am not using any measures in this visual. This data is coming straight from a database and at the moment it is just a basic count of Actions (based on their Action ID) and their average percentage completion.

[Nishantjain]

So you are using implicit measures which is not a good practice.

Do you know when you drop the average completion % field, is it "sum" or "count" of the field. You can find this by clicking on the small arrow next to average completion % field in the visual.

If it is sum (or something else) create a measure accordingly and use it with the formula I suggested above.

Let me know if it doesn't work

[bricwood]

I think there is a miscommunication or I failed to explain the data properly.

I am pretty new to Power BI so please bear with me..

If there is no data for that month then that month will not show up on the visual unless I select "Show items with no data" on the Visual's Date Column. This results in the image shown above which disregards the Visual's Relative date filtering.

Can you please advise an example of this Measure? Every dashboard I have ever made has always referenced the table's columns.

[Nishantjain]

Can you tell me how you are aggregating the table column. For example, when you drop average completion % in the visual, is it adding it or counting it

[bricwood]

I am using the Average Summarization to determine the average.

[Nishantjain]

@bricwood 

Use the following measures in your table and then use the relative date filter

Average Completion =
VAR keyMeasure =
    AVERAGE ( [Average Completion (%)] )
RETURN
    IF ( ISBLANK ( keyMeasure ), 0, keyMeasure )

[bricwood]

Hi @Nishantjain ,

This does show 0 for the blank months however the visual is still not adhering to the 13 Month Relative date filtering.

Thank you for the prompt replies.

[Nishantjain]

Do you still have "Show items with no data" active anywhere?

[bricwood]

@NishantjainI do not have this option selected on this Visual anymore.

Even when I adjust the Date filter it simply changes the Avg Completion to 0.00 and the # of Actions becomes blank.

[Nishantjain]

Can you also send the screenshot of the relative date filter

[bricwood]

@Nishantjainwould it be possible to use DAX in the Measure you helped me create to do a relative date filter?

[bricwood]

Hi@Nishantjain would it be possible to use DAX to filter the date of the Measure?

[bricwood]

I also get the same result using the Date Slicer, it retains all months of 2018 and 2019.