Rental days per month

[HPBeh]

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.

[Eric_Zhang · Accepted solution]

---

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

[HPBeh]

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