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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Microsoft Employee
Microsoft 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
Microsoft Employee
Microsoft 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
Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors