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 any i dea?

Thanks in advance!

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

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    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'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