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
thanish's avatar
thanish
Frequent Visitor
9 years ago

Hi Phil_Seamark, is there any solution for my query? I'm working on a strict deadline and got to complete it in a day or two.

  • 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.