Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
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.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |