cancel
Showing results 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

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 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.

1 ACCEPTED SOLUTION
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])

See more details in the attached pbix file.

2 REPLIES 2
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])

See more details in the attached pbix file.

Frequent Visitor

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!!

Announcements

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.

Power BI Monthly Update - February 2024

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

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

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
Top Kudoed Authors