Forum Discussion

thanish's avatar
thanish
Frequent Visitor
9 years ago

Data for days which are not present

Hi all, I need a help regarding the date selection for the date/months which are not present in the original data source.  I have been working on a Dashboard which has details about the rental perio...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi thanish

 

Sorry for the delay.

 

I created the following table as my starting point, which came through to my Power BI Desktop as Table3

 

 

I then created the following calculated table based on Table3

 

Utilisation Table = 
VAR c = ADDCOLUMNS(CALENDARAUTO(),"Month",FORMAT([Date],"MMM YY"),"Days",1,"MonthID" , INT(Format([Date],"YYYYMM")))
VAR Z = FILTER(CROSSJOIN('Table3',c),[Date]>=[Delivery Date] && [Date]<=[Collection Date])
RETURN SELECTCOLUMNS(Z,"ID",[ID],"Month",[Month],"MonthID",[MonthID])

I then added the following measure to the Utilisation Table.  

 

Count of Days = COUNTROWS('Utilisation Table')

I set the "sort by column" of the Month column to MonthID and then created the following bar chart

 

 

I think this might be close.....

 

thanish's avatar
thanish
Frequent Visitor
9 years ago

Hi Phil_Seamark, what is the use of the ID column in the table? I don't have an ID column in my table. Just Incase if I want to add I have more than 1000s rows.