Show dates with no data on continuous date axis

[leozuo-flex]

I have data in a table recording utilization of resources in a format like this:

Basically, we record the name of the resource, when the work has started, and for how long it was being worked.

And what I want to achieve is a chart with aggregated time the resource work each day, like this:

When no working activity has been recorded that day, it should be shown as 0.

But when I add the data to Power BI, it will show dates with no data as a line between other data points, not 0. Like this:

I have read elsewhere that I need to create a separate DateTable to summarize all the data, is that correct? But then, how can I dynamically add columns based on resource name existed in the source table? I don't want to modify the chart each time we add a C or D or E to this list, which could happen very frequently. Is there a way to achieve this?

[Phil_Seamark]

Hi @leozuo-flex

I do recommend you use a separate date table, but have you tried an Area chart?

[leozuo-flex]

Hi @Phil_Seamark No. I just tried area chart but it does not work either. The issue I'm having is, I don't know how can I construct a separate date table without knowing all resource names (Device) beforehand. Perhaps you could give me an example with the sample data shown above please?

[Phil_Seamark · Accepted solution]

Hi @leozuo-flex

Here is a link to a PBIX file that does what you are after.

https://1drv.ms/u/s!AtDlC2rep7a-oxJsoAdq92qNMpqo

Basically the problem is your orginal table simply lacks the necessary rows for the measures to work off.  So for device A, there is no row for the 2nd of Jan so it can't generate a value.  You cannot add rows to existing phyicical tables in DAX, but you can create new tables and plug the gaps in there.

The PBIX File I generated starts with your data in a table called 'Table'.  I then generate a new table with some DAX magic that fills gaps with zeros for days with no data. 

Hopefully you can follow and adapt to your own data.

[johankent30]

@Phil_Seamark This answer is very close to what I need however I am needing more than two columns returned in the GapFiller Variable. Example to what I am trying to do below. Is this possible? Thanks so much!!

VAR GapFiller = SELECTCOLUMNS(

GENERATE(VALUES('Table'[Device]),CALENDAR(MIN('Table'[Work Start]),MAX('Table'[Work Start]))) ,

"Device" , [Device] ,

--"Word Start", [Date] ,

--"Time Running",0 ,

"Date (Calculated)",[Date],

"ColumnA", [ColumnA],

"ColumnB", [ColumnB],

"ColumnC", [ColumnC])

[febc1992C]

hi @Phil_Seamark 

Please I need your help to solve a problem similar to this one. I have gone around the internet for 1 week without finding a solution, I think I'm losing my mind.

[Anonymous]

Try this: https://docs.microsoft.com/en-us/power-bi/desktop-show-items-no-data

[febc1992C]

@Phil_Seamark Please I need your help to solve a problem similar to this one. I have gone around the internet for 1 week without finding a solution, I think I'm losing my mind.

[febc1992C]

@Phil_Seamark Please I need your help to solve a problem similar to this one. I have gone around the internet for 1 week without finding a solution, I think I'm losing my mind.

[leozuo-flex]

Hi Phil,

Thanks for this example. I have modified it to work with my data, it works like a charm. I think I understand DAX better now on how to do these things 🙂