Forum Discussion

bricwood's avatar
bricwood
Icon for Helper I rankHelper I
7 years ago

Reset Cumulative Sum/Total Based on a Column Value

I have a set of data that I need to have a running total for but I need it to reset based on the value of another column.

 

The total will increment when one of the columns value is null, once it is not null the total will reset to zero then continue.

 

Additionally, I need to somehow reset it to zero or group it based on another column (like a category).

 

I have attached what I was trying to do but it is far out of my abilities...

 

 

Need Help

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    This measure expression gets your expected results from the example data.

    Count Since Last Yes =
VAR thisdatetime =
    MIN ( 'Action'[Date] )
VAR lastYes =
    CALCULATE (
        MAX ( 'Action'[Date] ),
        ALL ( 'Action'[Date] ),
        'Action'[Date] < thisdatetime,
        'Action'[Action] = "Yes"
    )
VAR ifblank =
    IF (
        ISBLANK ( lastYes ),
        CALCULATE ( MIN ( 'Action'[Date] ), ALLEXCEPT ( 'Action', 'Action'[Category] ) ) - 1,
        lastYes
    )
RETURN
    IF (
        MIN ( 'Action'[Action] ) <> "Yes",
        CALCULATE (
            COUNTROWS ( 'Action' ),
            ALL ( 'Action'[Date] ),
            'Action'[Date] <= thisdatetime,
            'Action'[Date] > ifblank
        ) - 1
    )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • bricwood's avatar
      bricwood
      Icon for Helper I rankHelper I
      7 years ago

      Greg_Decklerthat looks great but these rows are consecutive..

       

      Would you suggest I change the data in a way that would allow this to work? If so, any tips?

       

      Thank you for getting back to me.

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Yeah, I think if you add an Index column to this in Power Query that you are pretty much there. To be clear, Cthulhu doesn't work without consequetive data. There pretty much has to be an Index. The only wrinkle you have is that you have to account for the extra "Category" column but that shouldn't be too much of a stretch.

    • bricwood's avatar
      bricwood
      Icon for Helper I rankHelper I
      6 years ago

      I ended up making a Powershell script that did the work for me. Nothing else really worked.