Forum Discussion
Solved
Create measure based on start and end date, and parameter value
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...
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
PaulOlding
Solution Sage
Solution SageHi 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