Consecutive Days With Status Changes

[rogerthat]

Hi all,

I'm basically trying to replicate the below for consecutive days based on the ID and how long was the Status was consecutive based on Date

ID	Date	STATUS	CONSECUTIVE  DAYS
XYZ	1/1/2023	COMPLETE	1
BXY	1/1/2023	COMPLETE	1
XYZ	1/2/2023	COMPLETE	2
BXY	1/2/2023	COMPLETE	2
XYZ	1/5/2023	INCOMPLETE	1
XYZ	1/6/2023	INCOMPLETE	2

I do have an Index.

[rogerthat]

Thank you for providing solution  😀 !

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a calcualed measure.

It might be different if the source becomes mroe complicated, however, based on what I got from the sample, the below is one of many ways to achieve the solution.

Expected result measure: =
IF (
    HASONEVALUE ( Data[ID] ),
    CALCULATE (
        COUNTROWS ( Data ),
        WINDOW (
            1,
            ABS,
            0,
            REL,
            SUMMARIZE ( ALL ( Data ), Data[ID], Data[Date], Data[STATUS] ),
            ORDERBY ( Data[Date], ASC ),
            KEEP,
            PARTITIONBY ( Data[ID], Data[STATUS] )
        )
    )
)