Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Running total by group with a reset

Either in a Dax calculated column or in M/Power Query I need to create a running total that groups by a part number and resets if it goes below 0.    This is essentially projecting closing month in...
Need Help
  • FrankAT's avatar
    FrankAT
    5 years ago

    Hi Anonymous ,

    you can do it like this:

     

     

    Closing Inventory = 
CALCULATE (
    SUM ( 'Table'[Net Quantity] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Part] ),
        'Table'[Date] <= MAX ( 'Table'[Date] )
    )
)

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

FrankAT's avatar
FrankAT
Icon for Community Champion rankCommunity Champion
5 years ago

Hi Anonymous ,

you can do it like this:

 

 

Closing Inventory = 
CALCULATE (
    SUM ( 'Table'[Net Quantity] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Part] ),
        'Table'[Date] <= MAX ( 'Table'[Date] )
    )
)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Anonymous's avatar
Anonymous
Not applicable
5 years ago

This is just a grouped running total, unfortunately it does not take into account the reset so I cannot accept it as the solution.