cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thanish
Frequent Visitor

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 period of the equipment and their Invoiced date. Consider that an equipment is rented for 6 months. The usual scenario is that it has to be invoiced every month. In some scenarios, they are not invoiced for a particular month which does not mean that the equipment was not used.  

 

Please find the below screenshot for the data and the bar chart I have created using the data. As you can see that the rental period for the first 2 record is from 25th January-16 to 1st July-16 but they have been invoiced(created date) only for January and July. This does not mean that the equipment is not utilized for the in between months February, March, April, May and June. 

 

I created the bar chart with created date in the x-axis and the days it is utilized in the y-axis. Once I look at the chart it depicts me the wrong info as I mentioned above. Is it possible to add the months for which the invoice date is not present so that the graph should show value for the months it has not been invoiced ??? Your help is very much appreciated.

 

 Galmon query.png

6 REPLIES 6
thanish
Frequent Visitor

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.

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

 

dates.png

 

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

 

Util 2.png

 

I think this might be close.....

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.

 

Phil_Seamark
Microsoft
Microsoft

Hi @thanish

 

Yes this is possible.  What value would you like to be shown for Feb, Mar, April, May and June in this case?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi Phil, thanks for the response. I have used a formula to calculate the overall rental period which is the difference between the collection date and delivered date as you can see in the below date. When I plot the bar chart I wanted the total number of days it has been utilized for a particular month and % utilization too(which I think will work once the days are there). Right now it automatically extracts the days for the particular month and not for the months that are not present in the created date. Your help is appreciated. Thanks

 

powerBI ques.png 

thanish
Frequent Visitor

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 period of the equipment and their Invoiced date. Consider that an equipment is rented for 6 months. The usual scenario is that it has to be invoiced every month. In some scenarios, they are not invoiced for a particular month which does not mean that the equipment was not used.  

 

Please find the below screenshot for the data and the bar chart I have created using the data. As you can see that the rental period for the first 2 record is from 25th January-16 to 1st July-16 but they have been invoiced(created date) only for January and July. This does not mean that the equipment is not utilized for the in between months February, March, April, May and June. 

 

I created the bar chart with created date in the x-axis and the days it is utilized in the y-axis. Once I look at the chart it depicts me the wrong info as I mentioned above. Is it possible to add the months for which the invoice date is not present so that the graph should show value for the months it has not been invoiced ??? Your help is very much appreciated.

 

 Galmon query.png

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 episode 5 with aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt will talk about the importance of Data Modeling.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!