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

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
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])`

See more details in the attached pbix file.

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

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