Forum Discussion

leozuo-flex's avatar
leozuo-flex
Regular Visitor
8 years ago
Solved

Show dates with no data on continuous date axis

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....
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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.

     

     

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

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's avatar
johankent30
Helper I
7 years ago

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