Resetting Running Total based on column value

[Anonymous]

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. Here the example:



Any body any i dea?

Thanks in advance!

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

It is for creating a new column.

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

https://www.dropbox.com/s/0954h7ecsxkervx/hr303.pbix?dl=0 

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

[Anonymous]

@Jihwan_Kim  thanks for the solution. It works amazing! One more twist i am trying to figure out. Calculating the resetted running total but for a sub group (SKU) as shown in the screenshot: 


Target column in yellow:


Any idea? 

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Thank you for your feedback.

Please check the link down below.

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] )

https://www.dropbox.com/s/0954h7ecsxkervx/hr303.pbix?dl=0 

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