Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Resetting Running Total based on column value

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 a...
Need Help
Show and Tell
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
Jihwan_Kim
Super User
5 years ago

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