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 EARLIER to get some kind of reversed running sum with subtotals that are grouped on the 'Iteration' and 'User' column but I can't get it to work.

 

 

DateIterationUser Capacity per UserRemain Cap per Iteration
2017-06-08S1aa510
2017-06-09S1aa55
2017-06-10S1aa00
2017-06-11nullaa00
2017-06-12nullaa00
2017-06-13nullaa00
2017-06-08S1bb36
2017-06-09S1bb03
2017-06-10S1bb33
2017-06-11S2bb515
2017-06-12S2bb510
2017-06-13S2bb55
2017-06-08nullcc00
2017-06-09nullcc00
2017-06-10nullcc00
2017-06-11S2cc618
2017-06-12S2cc612
2017-06-13S2cc66

 

Any help is much appreciated.

 

Thanks!

 

Best,

Fredrik

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,

1 Reply

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