Forum Discussion

vajofr's avatar
vajofr
Regular Visitor
9 years ago
Solved

DAX - Reversed running total

  Hi,   I'm trying to create the column in red "Remain Cap per Iteration" but I can't figure out how to do the calculation. I think I  need to use the CALCULATE formula together with SUM and EARL...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    vajofr

     

    In this scenario, you just need to use group total minus the cumulative total. You can create a measure like below:

     

    Reverse Running Total =
CALCULATE (
    SUM ( Table2[Capacity per User] ),
    ALLEXCEPT ( Table2, Table2[User], Table2[Iteration] )
)
    - CALCULATE (
        SUM ( Table2[Capacity per User] ),
        ALLEXCEPT ( Table2, Table2[User], Table2[Iteration] ),
        FILTER (
            ALLEXCEPT ( Table2, Table2[User], Table2[Iteration] ),
            Table2[Date] < MAX ( Table2[Date] )
        )
    )

     

     

    Regards,

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

vajofr

 

In this scenario, you just need to use group total minus the cumulative total. You can create a measure like below:

 

Reverse Running Total =
CALCULATE (
    SUM ( Table2[Capacity per User] ),
    ALLEXCEPT ( Table2, Table2[User], Table2[Iteration] )
)
    - CALCULATE (
        SUM ( Table2[Capacity per User] ),
        ALLEXCEPT ( Table2, Table2[User], Table2[Iteration] ),
        FILTER (
            ALLEXCEPT ( Table2, Table2[User], Table2[Iteration] ),
            Table2[Date] < MAX ( Table2[Date] )
        )
    )

 

 

Regards,