summing time utilization

[Chief]

I have the following formula that is calculating the number of days an asset is available during a given month:

Days avail =

VAR fleetstart = COALESCE(MIN(RentalTable[FleetDate]), MIN(RentalTable[DateOut]), TODAY())

RETURN

COUNTROWS(INTERSECT(values(RentalContractCalendar[Date]),CALENDAR(fleetstart, TODAY())))

where RentalTable[FleetDate] - first day the machine was available for rent

RentalTable[DateOut] - first day machine went on rent

RentalContractCalendar[Date] - calendar reference

I am using this in conjunction with a created measure that counts the number of days the asset was on rent. Dividing the days on rent by days available gives me time utilization. 

My issue is that I can't get the above formula to sum. It is counting the total days available per asset but is not summing the total.

I know I somehow need to summarize this function in order to count the total, I just don't know the dax I need to do this.

 The below is the output of the formula above. I need the total to show 984, not 366. Help!?

[kushanNa]

can you try this measure ? 

RentalContract_TotalCount = 
SUMX(
    VALUES(RentalTable[Stock#]), 
    VAR fleetstart = MINX(FILTER(RentalTable, RentalTable[Stock#] = EARLIER(RentalTable[Stock#])), RentalTable[DateOut])
    VAR fleetend = TODAY()
    RETURN 
        CALCULATE(
            COUNT(RentalContractCalendar[Date]),
            RentalContractCalendar[Date] >= fleetstart &&
            RentalContractCalendar[Date] <= fleetend
        )
)

[Chief]

It appears this measure sums inaccurately. When I run it, I see 619 days for calendar year 2024 when I should see 366. The fleet start date on this unit was 7/10/23, thus when I pull in year 2023 it counts 175 days for 2023 and 619 for 2024.

[kushanNa]

Hi 

Try this measure ? 

DaysAvail1 = 
VAR fleetstart = COALESCE(MIN(RentalTable[FleetDate]), MIN(RentalTable[DateOut]), TODAY())

RETURN 
SUMX(
    VALUES(RentalTable[Stock#]),  
    COUNTROWS(INTERSECT(VALUES(RentalContractCalendar[Date]), CALENDAR(fleetstart, TODAY())))
)

[Chief]

Thanks @kushanNa ! This is  SO close. you can see that the DaysAvail column is summing the 5 entries at the 366 days for each column (total of 1830). I would like it to total 1,662 - the actual days available of the 5 entries. Any ideas?

[Tutu_in_YYC]

Try:

SUMX(
   ALLSELECTED('Table'[Stock#]),
   [Days Avail]
) 

[Chief]

Tutu - this link is my rental table. I tried your suggestion but the calculation totals all lines, not just the total. Maybe seeing the visual will help understand my desired outcome. When one of the assets is filtered, everything works fine. When the filter is removed, the DaysonRent calcuate a total like they should, the daysavail don't total. Any help is appreciated:

https://drive.google.com/file/d/1e3TdNDOW7pmDy6t90QfAzqRP3SX12hIA/view?usp=sharing

[Tutu_in_YYC · Accepted solution]

This is quick and dirty.

Measure =
IF( 
    ISINSCOPE(RentalTable[Stock#]),
    [DaysAvail],
    SUMX(
    ALLSELECTED('RentalTable'[Stock#]),
    [DaysAvail]
    )
)

I'd recommend creating star schema for better scalable DAX.

[Chief]

Gold Star Tutu!!! This works.

[Tutu_in_YYC]

looks like @kushanNa is doing a good job helping. @Chief Please reply here if further help needed.