Reset Cumulative Sum/Total Based on a Column Value

[bricwood]

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

[mahoneypat]

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

[himanshuxa720]

did you get the answer to this?

[bricwood]

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

[Greg_Deckler]

Not positive, but this sounds like you are in need of something along the lines of Cthulhu: https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/td-p/509739

[bricwood]

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

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.