counting consecutive identical values - calculated column

[BST]

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

[v-yuaj-msft · Accepted solution]

Hi @BST ,

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

x2

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

[BST]

thank you very much - just what I was looking for!

[BST]

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:

[amitchandak]

@BST ,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

[BST]

Thanks - I'll test it later today and let you know if it worked.

Bo