Determining fleet size with many variables

Hello AlienBI 

Instead of creating a large intermediate table using GENERATE, you should calculate fleet size directly based on the selected date from your calendar. The key idea is that for any given date, a vehicle is considered “in fleet” if the selected date falls between its In Service Date and Out of Fleet Date. On top of that, you need to dynamically decide the location of that vehicle depending on whether the selected date is before or after the Due Back Date. This dynamic location cannot be stored physically in the model because it changes per date, so it has to be created virtually inside the measure.

Here is the complete working approach in one go

Fleet Size Final =
VAR SelectedDate = MAX('Calendar'[Date])

VAR BaseTable =
ADDCOLUMNS(
'Fleet',
"Dynamic Location",
IF(
SelectedDate < 'Fleet'[Due Back Date],
'Fleet'[Current Location],
'Fleet'[Expected Return Location]
)
)

RETURN
CALCULATE(
DISTINCTCOUNT('Fleet'[Unit #]),

FILTER(
BaseTable,
'Fleet'[In Service Date] <= SelectedDate &&
'Fleet'[Out of fleet date] >= SelectedDate
),

TREATAS(
VALUES('DimLocation'[Location]),
[Dynamic Location]
),

TREATAS(
VALUES('DimCarClass'[Class]),
'Fleet'[Class]
)
)

This solves both of your problems together. The movement logic now works correctly because the location is evaluated dynamically for each date instead of being fixed in a calculated table, so if a vehicle jumps from stage/location 1 directly to 3 or 4, it still behaves correctly. At the same time, your filtering issue is resolved because you are no longer relying on a single physical relationship; instead, you use proper dimension tables for Location and Car Class and apply them using TREATAS, which allows both filters to work simultaneously even though the location is dynamically computed.

The reason your original approach was failing is because calculated tables and columns are evaluated at data refresh time, not at query time, so they cannot respond properly to slicers like date, location, or class. Also, using GENERATE with Calendar creates a heavy dataset and complicates relationships, which is unnecessary here.