Forum Discussion

Chief's avatar
Chief
Helper II
1 year ago
Solved

summing time utilization

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(RentalTa...
Need Help
  • Tutu_in_YYC's avatar
    Tutu_in_YYC
    1 year ago

    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.

kushanNa's avatar
kushanNa
Super User
1 year ago

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's avatar
    Chief
    Helper II
    1 year ago

    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.