Create measure based on start and end date, and parameter value

[rubentj]

Hello guys - so i've been stuck on a problem, and I'm not sure it's solvable in the way I want to. 

I want to calculate utilization on rental cars, and the problem is that the number of available cars on various locations is changing at all times. So basically, I have a problem with three dimensions. 

Let me describe the challenge: 

Let's say a car is located at location X - this is an estimation of where the car is located in the future.

Example: Car 1 is planned located at location X from (1'Jan'2021) until 31'Dec'2021. 

Then, there is a change in the plans. The plans are changing 1'Feb'2021 (Index 3 in sample data) - and the changes is that the car is planned located at location Y from 1'Mar'2021 until 31'May'2021, and relocated back at location X from 1'Jun'2021 until 31'Dec'2021 (Index 4 in sample data). This means that the original plan also changes (index 2 in sample data).

Here are some example data: 

Index	Plan#	CarID	CarClass	Location	StartDateLocation	EndDateLocation	Created	LastChanged
1	1	100	BK	X	01-01-2021	12-31-2021	01-01-2021	02-01-2021
2	1	100	BK	X	01-01-2021	01-31-2021	02-01-2021	null
3	2	100	BK	Y	02-01-2021	05-31-2021	02-01-2021	null
4	2	100	BK	Y	06-01-2021	12-31-2021	02-01-2021	null

So the tricky part here: If i want to know how many cars there was at location X, based on the knowledge we had mid January, i.e. 15'Jan'2021, I want the result to be that there is one car for the whole year (that was the knowledge I had at that time). 15'Jan'2021 is a parameter input (slicer). 

Further, if I want to know how the situation was at 15'Feb, I want the result to be one car at Location X from 1'Jan-31'Jan, one car at location Y between 1'Feb'2021-31'May'2021, and one car between 1'Jun'2021-31'Dec'2021. 

This is input in utilization calculation. I want to put this into a line chart, where I can change the parameter input to vary. Anyone got a suggestion? I have been searching around for a week without finding any suitable way. I can generate a new table listing all dates the car is at certain locations, per every unique plan, but the dataset will be very large and not manageable. 

[PaulOlding · Accepted solution]

Hi @rubentj 

This article discussing data with start and end dates. https://www.daxpatterns.com/events-in-progress/ 

You have an added complication where you want an as at date selection too.  This measure uses 2 disconnected date tables, one to use as a slicer & the other to use in a chart.

Car Count = 
VAR _AsAtDate = MAX('As At Date'[Date])
VAR _CurrentLocationDate = MAX('Location Date'[Date])
VAR _Result = 
CALCULATE(
	COUNTROWS('Table'),
	'Table'[Created] <= _AsAtDate,
	(ISBLANK('Table'[LastChanged]) || 'Table'[LastChanged] > _AsAtDate),
	'Table'[StartDateLocation] <= _CurrentLocationDate,
    'Table'[EndDateLocation] > _CurrentLocationDate
)
RETURN
    _Result

[rubentj]

Wow! Thank you so much @PaulOlding ! This is magic!!!