Show a Zero for data within a date range that is not there?

[MelissaRegnaud]

Hello There!

A question regarding data within a date range slider. If I have data /records for dates 1 Jan , 2 Jan and 5 Jan for incidents that occur and no data for 4 Jan , is there a way to show this as 0 on the visualisation layer that 0 Incidents happened on that date if its not in the data? Something like within the date range of 31 Dec - 30 Jan ( as the slider) if no Date show 0 Value? 

I received the suggestion to add it into the data as in every single day that there is no incident add it in and make  it 0 for that date in the data file?

I would however prefer to have the change in the visual layer, If ..then... else... kinda of solution? rather than within the data file ? 

Best regards

Melissa

[v-yulgu-msft · Accepted solution]

Hi @MelissaRegnaud,

Please create a calendar table first. Establish an active relationship between these two tables.

calendar = CALENDAR(DATE(2016,12,1),DATE(2017,5,31))

Add 'calendar'[date] into slicer. Add 'calendar'[date] and incident column into table visual. Enable to "show item with no data".

With this method, you can show blank for those records with no data. But cannot display a "0".

To display a "0", I think you have to add it into table view rather than visual layer.

Create a calculated table like below, and add columns from this table into slicer and table visual.

New table =
UNION (
    'Incident data',
    ADDCOLUMNS (
        EXCEPT ( VALUES ( 'calendar'[Date] ), VALUES ( 'Incident data'[Date] ) ),
        "incident", 0
    )
)

Best regards,

Yuliana Gu