Forum Discussion
counting consecutive identical values - calculated column
Hello master minds ,
I have a Power BI data set with TimeStamps.
Each are 15 minutes apart.
I would like to create a column that counts the number of consecutive timestamps with the same StatecategoryName.
This is an Excel sample of my data.
The results I want to calculate are in the last column "Consecutive Identical Values".
Thanks in advance
Bo
| TimeStampLocal | StateCategoryName | Index | Consecutive identical values | |
| 03-10-2021 | 00:00 | Downtime | 0 | 2 |
| 03-10-2021 | 00:15 | Downtime | 1 | 1 |
| 03-10-2021 | 00:30 | Uptime | 2 | 1 |
| 03-10-2021 | 00:45 | Downtime | 3 | 2 |
| 03-10-2021 | 01:00 | Downtime | 4 | 1 |
| 03-10-2021 | 01:15 | Uptime | 5 | 5 |
| 03-10-2021 | 01:30 | Uptime | 6 | 4 |
| 03-10-2021 | 01:45 | Uptime | 7 | 3 |
| 03-10-2021 | 02:00 | Uptime | 8 | 2 |
| 03-10-2021 | 02:15 | Uptime | 9 | 1 |
| 03-10-2021 | 02:30 | Downtime | 10 | 16 |
| 03-10-2021 | 02:45 | Downtime | 11 | 15 |
| 03-10-2021 | 03:00 | Downtime | 12 | 14 |
| 03-10-2021 | 03:15 | Downtime | 13 | 13 |
| 03-10-2021 | 03:30 | Downtime | 14 | 12 |
| 03-10-2021 | 03:45 | Downtime | 15 | 11 |
| 03-10-2021 | 04:00 | Downtime | 16 | 10 |
| 03-10-2021 | 04:15 | Downtime | 17 | 9 |
| 03-10-2021 | 04:30 | Downtime | 18 | 8 |
| 03-10-2021 | 04:45 | Downtime | 19 | 7 |
| 03-10-2021 | 05:00 | Downtime | 20 | 6 |
| 03-10-2021 | 05:15 | Downtime | 21 | 5 |
| 03-10-2021 | 05:30 | Downtime | 22 | 4 |
| 03-10-2021 | 05:45 | Downtime | 23 | 3 |
| 03-10-2021 | 06:00 | Downtime | 24 | 2 |
| 03-10-2021 | 06:15 | Downtime | 25 | 1 |
Hi Anonymous ,
Based on your description, you can create some columns as follows.
Mark =
var x1=MAXX(FILTER('Consecutive',[Index]=EARLIER([Index])-1),[StateCategoryName])
var x2=IF([StateCategoryName]=x1,0,1)
return
x2Category = SUMX(FILTER(ALL('Consecutive'),[Index]<=EARLIER('Consecutive'[Index])),[Mark])Consecutive_Test = RANKX(FILTER(ALL('Consecutive'),[Category]=EARLIER(Consecutive[Category])),[Index],,DESC)Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Anonymous ,Try to check this solution, if that can help
https://community.powerbi.com/t5/Desktop/Count-last-actual-consecutive-sorted-rows/m-p/151191#M65364
It is very nice and very close to what I am looking for, but I have trouble nailing the last bit.
I replaced the 'Table1[Date] with my 'index'-columm as date-level is too coarse.
The result is the accumulative sum for the period in question.
It does not reset each time the statecategory changes.
Probably I need to add another condition but am not sure how and which.
Here is example with the results from the new column:
Hi Anonymous ,
Based on your description, you can create some columns as follows.
Mark =
var x1=MAXX(FILTER('Consecutive',[Index]=EARLIER([Index])-1),[StateCategoryName])
var x2=IF([StateCategoryName]=x1,0,1)
return
x2Category = SUMX(FILTER(ALL('Consecutive'),[Index]<=EARLIER('Consecutive'[Index])),[Mark])Consecutive_Test = RANKX(FILTER(ALL('Consecutive'),[Category]=EARLIER(Consecutive[Category])),[Index],,DESC)Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
