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

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

    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?