Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.