Solved: Reset running total by column value and calculated...

Anonymous · ‎06-13-2021

Hi guys,

i have a running total of a sales column. This running total shall start to cumulate sales from 0 after the columnes where the reset column is 1. The whole cumulation should be done by group for the SKU column:

Here the example with the yellow colum as output target:

Anybody any i dea?

Thanks in advance!

mahoneypat · ‎06-13-2021

I gave you the measure version. Here is the modified version that should work in a calculated column.

RT Since Reset =
VAR thisdate = Reset[Date]
VAR thissku = Reset[SKU]
VAR lastresetdate =
    CALCULATE (
        MAX ( Reset[Date] ),
ALL(Reset),

Reset[SKU] = thissku,
        Reset[Date] <= thisdate,
        Reset[Reset] = 1
    )
VAR result =
    CALCULATE (
        SUM ( Reset[Sales] ),
        ALL ( Reset ),
        Reset[SKU] = thissku,
        FILTER (
            ALL ( Reset[Date] ),
            Reset[Date] >= lastresetdate
                && Reset[Date] <= thisdate
        )
    )
RETURN
    result

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

View solution in original post

Jihwan_Kim · ‎06-13-2021

Hi, @Anonymous

Please try the below for creating a new column.

Sales Cumulate Reset CC =
VAR resetcumulate =
CALCULATE (
SUM ( 'Table'[Reset] ),
FILTER (
'Table',
EARLIER ( 'Table'[Date] ) >= 'Table'[Date]
&& EARLIER ( 'Table'[SKU] ) = 'Table'[SKU]
)
)
VAR currentdate = 'Table'[Date]
VAR resetcumulatetable =
FILTER (
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[Date], 'Table'[SKU], 'Table'[Sales] ),
"@resetgroup",
CALCULATE (
SUM ( 'Table'[Reset] ),
FILTER (
'Table',
EARLIER ( 'Table'[Date] ) >= 'Table'[Date]
&& EARLIER ( 'Table'[SKU] ) = 'Table'[SKU]
)
)
),
[@resetgroup] = resetcumulate
&& 'Table'[Date] <= currentdate
&& EARLIER ( 'Table'[SKU] ) = 'Table'[SKU]
)
RETURN
SUMX ( resetcumulatetable, 'Table'[Sales] )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Click here to visit my LinkedIn page

parry2k · ‎06-13-2021

@Anonymous just curious how come row 6 and 11 has a value of 2, isn't it should be 1 since it is reset at the previous date?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

mahoneypat · ‎06-13-2021

Here is one measure expression that returns your desired results.

RT Since Reset =
VAR thisdate =
    MAX ( Reset[Date] )
VAR thissku =
    MIN ( Reset[SKU] )
VAR lastresetdate =
    CALCULATE (
        MAX ( Reset[Date] ),
        ALL ( Reset ),
        Reset[SKU] = thissku,
        Reset[Date] <= thisdate,
        Reset[Reset] = 1
    )
VAR result =
    CALCULATE (
        SUM ( Reset[Sales] ),
        ALL ( Reset ),
        Reset[SKU] = thissku,
        FILTER (
            ALL ( Reset[Date] ),
            Reset[Date] >= lastresetdate
                && Reset[Date] <= thisdate
        )
    )
RETURN
    result

Pat