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
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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.