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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.