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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors