Is there a way to achieve a Selected row based cumulative sum using DAX?

[rpai27]

I already have the data sorted and indexed. Is there a way to do the cummulative sum using DAX for the set of row that have the state set to True? 

Something like this:

Any help is greatly appreciated! 

Note:

1. The data doens't have data field that's why I created the index field for the cummulative sum and getting the previous state value on BI.

2. I saw an example of this being done using m-code but my dataset is too big to incorporate that. The powerQuery never loaded and crashed the report. 😞 And for some reason I get better performance writing DAX than having the caculations done on powerQuery.

[mahoneypat · Accepted solution]

Please try this column expression.

CumulativeIndex =
VAR vThisIndex = State[Index]
VAR vLastFalse =
    CALCULATE (
        MAX ( State[Index] ),
        State[State]
            = FALSE (),
        State[Index] <= vThisIndex
    )
VAR vSum =
    CALCULATE (
        SUM ( State[Index] ),
        State[Index] <= vThisIndex,
        State[Index] > vLastFalse
    )
RETURN
    IF (
        State[State]
            = TRUE (),
        vSum
    )

Regards,

Pat

[rpai27]

Thank you @AlB @mahoneypat 

For anyone looking for a solution to a similar problem, here's the DAX formula after implementing ALL(Sample_data) suggested by

CumulativeIndex =
VAR vThisIndex = Sample_data[Index]
VAR vLastFalse =
CALCULATE (
MAX (Sample_data[Index] ),ALL(Sample_data),
Sample_data[State]
= FALSE (),
Sample_data[Index] <= vThisIndex
)
VAR vSum =
CALCULATE (
SUM ( Sample_data[Data]),ALL(Sample_data),
Sample_data[Index] <= vThisIndex,
Sample_data[Index] > vLastFalse
)
RETURN
IF (
Sample_data[State]
= TRUE (),
vSum
)

[AlB · Accepted solution]

@rpai27 

You need to add  ALL(Sample_Data) to the CALCULATE in both vLastFalse and vSum to account for the effects of context transition

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[mahoneypat · Accepted solution]

Please try this column expression.

CumulativeIndex =
VAR vThisIndex = State[Index]
VAR vLastFalse =
    CALCULATE (
        MAX ( State[Index] ),
        State[State]
            = FALSE (),
        State[Index] <= vThisIndex
    )
VAR vSum =
    CALCULATE (
        SUM ( State[Index] ),
        State[Index] <= vThisIndex,
        State[Index] > vLastFalse
    )
RETURN
    IF (
        State[State]
            = TRUE (),
        vSum
    )

Regards,

Pat

[rpai27]

I modified the code a little bit to sum of the Data field instead of the index on line 12. But it doesn't look like it's doing the cummulative sum. Looks like it's just showing the data from the data field.