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(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!?

 

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.

9 Replies

  • 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.

       

  • Tutu_in_YYC's avatar
    Tutu_in_YYC
    Super User
    1 year ago

    Try:

    SUMX(
       ALLSELECTED('Table'[Stock#]),
       [Days Avail]
    ) 
    • Chief's avatar
      Chief
      Helper II
      1 year ago

      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

       

  • 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?