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
HPBeh
Frequent Visitor

Rental days per month

Fairly new to PBI I am struggling with a problem and I hope someone can help me out.

 

Cars are rented and have a Rental Start Date and a Rental End Date.

The data may look like this (dates in European format):

 

 Rental Start DateRental End Date
Car 122-4-201725-4-2017
Car 128-4-20172-5-2017
Car 128-5-20171-1-1753

Date 1-1-1753 stands for zero in the DB and means rented up till now.

The number of days per month a car is rented has to be shown.

So the chart has to show 8 days in april, 6 in may, 30 in june and 31 (current date is 31 july) in july.

 

Thanks in advance for any guidance.

 

1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee


@HPBeh wrote:

Fairly new to PBI I am struggling with a problem and I hope someone can help me out.

 

Cars are rented and have a Rental Start Date and a Rental End Date.

The data may look like this (dates in European format):

 

  Rental Start Date Rental End Date
Car 1 22-4-2017 25-4-2017
Car 1 28-4-2017 2-5-2017
Car 1 28-5-2017 1-1-1753

Date 1-1-1753 stands for zero in the DB and means rented up till now.

The number of days per month a car is rented has to be shown.

So the chart has to show 8 days in april, 6 in may, 30 in june and 31 (current date is 31 july) in july.

 

Thanks in advance for any guidance.

 


@HPBeh

You can at first create a calculated column

Rental End Date 2 = IF('table'[Rental End Date]=DATE(1753,1,1),TODAY(),'table'[Rental End Date])

Then create a calendar table

calendar = CALENDAR("2016-01-01","2017-12-31")

Then create a new calendar table and put the fileds to a visual from the new table

Table 2 = FILTER(CROSSJOIN('table','calendar'),'calendar'[Date]>='table'[Rental Start Date]&&'calendar'[Date]<='table'[Rental End Date 2])

Capture.PNG

 

See more details in the attached pbix file.

 

View solution in original post

2 REPLIES 2
Eric_Zhang
Employee
Employee


@HPBeh wrote:

Fairly new to PBI I am struggling with a problem and I hope someone can help me out.

 

Cars are rented and have a Rental Start Date and a Rental End Date.

The data may look like this (dates in European format):

 

  Rental Start Date Rental End Date
Car 1 22-4-2017 25-4-2017
Car 1 28-4-2017 2-5-2017
Car 1 28-5-2017 1-1-1753

Date 1-1-1753 stands for zero in the DB and means rented up till now.

The number of days per month a car is rented has to be shown.

So the chart has to show 8 days in april, 6 in may, 30 in june and 31 (current date is 31 july) in july.

 

Thanks in advance for any guidance.

 


@HPBeh

You can at first create a calculated column

Rental End Date 2 = IF('table'[Rental End Date]=DATE(1753,1,1),TODAY(),'table'[Rental End Date])

Then create a calendar table

calendar = CALENDAR("2016-01-01","2017-12-31")

Then create a new calendar table and put the fileds to a visual from the new table

Table 2 = FILTER(CROSSJOIN('table','calendar'),'calendar'[Date]>='table'[Rental Start Date]&&'calendar'[Date]<='table'[Rental End Date 2])

Capture.PNG

 

See more details in the attached pbix file.

 

Hi Eric, great solution with just a few functions, there is still a lot to learn. Crossjoin is very new to me but it works fine. Thanks very much!!

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.