Determining fleet size with many variables

[AlienBI]

Hi there, 

POV: car rental business with fleet sometimes moving from one location to another. 
I've a fleet file which gives me the following data:

From this what I'm trying to calculate is the number of vehicles at each location, for each class for every date in the calendar (separate table). 

I managed to calculate this pretty well using the dax formula below in a new column, however what I'm struggling with is the following:
- getting my fleet movement right - the logic is if the calendar date is < Due Back Date, then use Current Location, otherwise use Expected Return Location. At the moment the formula below does not work to account for this (but I suspect it is linked to my connections, see next problematic)
- I need to link this table to other tables to get my location and car class filters working. As we can only have one connection between tables, it either only filters on locations or car classes - any idea how I can link both so my filters work?

Fleet size =

VAR tmpFleet =

ADDCOLUMNS('FleetReporting Fleet report - A',"In fleet",'FleetReporting Fleet report - A'[In Service Date],"Out of fleet",'FleetReporting Fleet report - A'[Out of fleet date])

VAR tmpTable =  

SELECTCOLUMNS(

    FILTER(

        GENERATE(

            tmpFleet,

            'Calendar'

        ),

        [Date] >= INT ([In fleet]) &&

        [Date] <= INT ([Out of fleet])

    ),

    "ID", 'FleetReporting Fleet report - A'[Unit #],

    "Car class",'FleetReporting Fleet report - A'[Class],

    "Date",[Date],

    "Location",if( Date]<'FleetReporting Fleet report - A'[Due Back Date],'FleetReporting Fleet report - A'[Current Location],

    'FleetReporting Fleet report - A'[Expected Return Location])

)

VAR tmpTable1 = GROUPBY(tmpTable,[ID],[Car class],[Location],"Count",COUNTX(CURRENTGROUP(),[Date]))

RETURN COUNTROWS(tmpTable1)

Thanks for your help

[pankajnamekar25]

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.

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn

[AlienBI]

hi @pankajnamekar25 

Thanks a lot for this - it makes a lot of sense. But I'm having an issue with TREATAS as it requires a fully qualified column reference as argument number 2 - see error below. 

[v-veshwara-msft · Accepted solution]

Hi @AlienBI ,

Thanks for reaching out to Microsoft Fabric Community.

I was able to reproduce your scenario in my environment and validate the behavior.

The issue with TREATAS occurs because it requires a fully qualified column reference from the model. In this case, the location is being calculated dynamically inside the measure, so it cannot be used directly with TREATAS.

To address this, the location logic needs to be evaluated within the measure and the filters applied inside the FILTER context.

Below is a working measure that resolved the issue:

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 &&
        [Dynamic Location] IN VALUES('DimLocation'[Location]) &&
        'Fleet'[ Class ] IN 
            FILTER(
                VALUES('DimCarClass'[Class]),
                NOT ISBLANK('DimCarClass'[Class])
            )
    )
)

With this approach, the fleet movement logic works correctly. A vehicle is counted under its current location before the due back date and switches to the expected return location after that date.

I have also included screenshots demonstrating the expected behavior along with the PBIX file for your reference.

Hope this helps. Please reach out for further assistance.
Thank you.

[AlienBI]

Thanks heaps, that works like a charm!