Cumulative/Running Total that resets after other column hits zero

[brunomoriya]

Hi everyone!

I tried some solutions for cumulative totals from other posts, but cant solve my problem.

The following table as an example what i need.

I have different dates/time and in/out description, for many 'Types' (AHKW, YYKW as example).

'Running Total Quantity' is calculated column:

Running Total Quantity = CALCULATE (

SUM ( 'Table'[Quantity] ),

FILTER (

ALLEXCEPT( 'Table', 'Table'[Type]),

'Table'[Date] <= EARLIER( 'Table'[Date] )))

When i try the same DAX for $ Totals i get 100,00 (AHKW example) but should be 0 because cumulative quantity is 0. As a result, the next sum date (-6560,00) is wrong, and should be 6600,00.

What i need is the $ Running Total like the red column 'What should be'.

Many thanks!!

[v-yingjl]

Hi @brunomoriya ,

Based on your sample data table, your calculated column seems work fine, the cumulative quantity of totals like -5200 + 5300 just be 100, why or how could it should be 0? If you want to get the red column result, what is its calculated logic... not certain about it...

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[brunomoriya]

hey @v-yingjl !

-5200 + 5300 is $$$ value, and running total is correct to be +100,

but the logic is when Running total for QUANTITY is zero, Running Total for $ is zero and resets, returning the first $$ value and keeps summing until Running total for quantity is zero again.

[v-yingjl · Accepted solution]

Hi @brunomoriya ,

You can create these two calculated columns:

Running Total Quantity = 
VAR _date = [Date]
VAR _type = [Type]
VAR tab =
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[Date],
            'Table'[IN / OUT],
            'Table'[Type],
            'Table'[Quantity]
        ),
        "Qty", IF ( [IN / OUT] = "OUT", 0 - [Quantity], [Quantity] )
    )
RETURN
    SUMX ( FILTER ( tab, [Type] = _type && [Date] <= _date ), [Qty] )

Running Total = 
VAR _date = [Date]
VAR _type = [Type]
VAR tab =
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[Date],
            'Table'[IN / OUT],
            'Table'[Type],
            'Table'[Totals],
            'Table'[Running Total Quantity]
        ),
        "total", IF (
            [Running Total Quantity] = 0,
            VAR _t = [Type]
            VAR _d = [Date]
            RETURN
                ABS (
                    CALCULATE (
                        MAX ( 'Table'[Totals] ),
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[Type] = _t
                                && 'Table'[Date]
                                    = CALCULATE (
                                        MAX ( 'Table'[Date] ),
                                        FILTER ( ALL ( 'Table' ), 'Table'[Type] = _t && 'Table'[Date] < _d )
                                    )
                        )
                    )
                ),
            [Totals]
        )
    )
RETURN
    SUMX ( FILTER ( tab, [Type] = _type && [Date] <= _date ), [total] )

The result table will be like this:

Attached sample file that hopes to help you, please check and try it: Cumulative/Running Total that resets after other column hits zero.pbix 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[brunomoriya]

Hi again @v-yingjl,

I was analysing all my original data after applying that solution and i got a situation here!

I got a Type 'KKZW' that ends in 20/04/2020 and there is no more "IN's" after that, differente from other Types that have new "IN's" after quantity hits zero.

Its looks like is getting the first date of each type!

I will try to fix that DAX Solution, but i will appreciate any help!

[brunomoriya]

woooowww @v-yingjl you are a ninja!

that worked great! super thanks!

[danextian]

Hi @brunomoriya ,

Try this please:

Running Total =
CALCULATE (
    SUM ( 'Table'[Quantity] ),
    ALLEXCEPT ( 'Table', 'Table'[Type] ),
    'Table'[Date] <= EARLIER ( 'Table'[Date] )
)

or

Running Total =
CALCULATE (
    SUM ( 'Table'[Quantity] ),
    ALL (
        'Table',
        'Table'[Type] = EARLIER ( 'Table'[Type] )
            && 'Table'[Date] <= EARLIER ( 'Table'[Date] )
    )
)

[AllisonKennedy]

It looks like this is a problem with your $ Totals column then, not the Running Total $. The Running Total $ is doing exactly as requested, and taking the -52000 from $ Totals column and adding 5300. How would you like it to calculate instead? Is there a standard $/Unit you want to use?

[Anonymous]

Hi, is it possible for you to provide a same sample code in excel power query ?  The result should reset the running total to 0 and start doing running total when value in other field (qty) is zero.

Regards,

Haresh

[brunomoriya]

Hi@AllisonKennedy !

The column 'Running Total $' is just a reference, is doing right as you said, but what i need its when 'Running Total QUANTITY' hits zero (because IN - OUT = zero , 2 hours and half later), 'Running Total $" is zero, and resets the 'Running Total $', returning first value for '$ Total' and keep summing until 'Running Total QUANTITY' hits zero again.

The red squares in data sample shows when Running Totals QUANTITY hits zero, $$ should be zero, and resets again, returning the earliest $ value, and keep summing until Running  Totals QUANTITY is zero again.

Not sure if im clear, sorry for bad english 🙂