Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
14 | |
12 | |
10 | |
10 |