column with a cumulative sum for an id

[JGM]

Hello,

I'm trying to build a cumulative sum of a flag for a specif id, as the following:

ID	Date	Flag	Consecutive
104945	27/01/2022	0	0,00
104945	03/01/2022	1	1,00
104945	19/11/2021	0	0,00
104945	27/09/2021	1	3,00
104945	20/09/2021	1	2,00
104945	01/09/2021	1	1,00
104945	31/05/2021	0	0,00

the idea is to see, how many times in a row have the flag appeared.

Any idea of how could i do it in powerBI?

Thank you!

[v-rongtiep-msft · Accepted solution]

Hi @JGM ,

I intercepted some data from your dataset. Please refer to my pbix to see if it helps you.

Create a measure.

Measure2 =
VAR _month =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            CALCULATETABLE ( 'Table', ALL ( 'Table' ), 'Table'[Flag ] = 0 ),
            'Table'[Date] <= SELECTEDVALUE ( 'Table'[Date] )
        )
    )
VAR you0_ =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] <= SELECTEDVALUE ( 'Table'[Date] )
                && 'Table'[Date] > _month
                && 'Table'[ID] = SELECTEDVALUE ( 'Table'[ID] )
        )
    ) + 0
VAR _one =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ID] = SELECTEDVALUE ( 'Table'[ID] )
                && 'Table'[Flag ] <> 0
                && 'Table'[Date] <= SELECTEDVALUE ( 'Table'[Date] )
        )
    )
VAR answer_ =
    CALCULATE (
        MIN ( 'Table'[Flag ] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ID] = SELECTEDVALUE ( 'Table'[ID] )
                && 'Table'[Date] <= SELECTEDVALUE ( 'Table'[Date] )
        )
    )
RETURN
IF ( answer_ = 1, _one, you0_ )

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-rongtiep-msft]

Hi @JGM , 

Please have a try.

Create a measure.

Measure = CALCULATE(SUM('Table'[Flag ]),FILTER(ALL('Table'),'Table'[ID]=SELECTEDVALUE('Table'[ID])&&'Table'[Date]<=SELECTEDVALUE('Table'[Date])))

If I have misunderstood your meaning, please contact me and provide your desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JGM]

First of all thank you for answering,

but it is not exactly what i was looking for, 

My idea is to set a counter to all the consecutive flags until one is 0 again and then reset the counter, something like this:

if you had any idea, i have not yet find the answer.

Thank you!
Best regards.

[v-rongtiep-msft]

Hi @JGM ,

Please have a try.

Create a measure.

Measure2 = 
VAR _month =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            CALCULATETABLE ( 'Table', ALL ( 'Table' ), 'Table'[Flag ] = 0 ),
            'Table'[Date] <= SELECTEDVALUE ( 'Table'[Date] )
        )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] <= SELECTEDVALUE ( 'Table'[Date] )
                && 'Table'[Date] > _month
        )
    ) + 0

If I have misunderstood your meaning, please contact me and provide your desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[JGM]

Hi,

The measure only works if i only have one ID,
Once i use the measure in my table with different IDs it doesn´t work.

Here is the dataset i´m using, unfortunetly i can't attach it directly:
https://1drv.ms/x/s!At9hYx7fXW7ZlDHy--dFktu2FcY_?e=hM9BQY

 Thank you for helping me.