Consecutive Days With Status Changes

Question

Hi all,&nbsp;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 DateIDDateSTATUSCONSECUTIVE&nbsp; DAYSXYZ1/1/2023COMPLETE1BXY1/1/2023COMPLETE1XYZ1/2/2023COMPLETE2BXY1/2/2023COMPLETE2XYZ1/5/2023INCOMPLETE1XYZ1/6/2023INCOMPLETE2&nbsp;I do have an Index.

jihwan_kim · Accepted Answer

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.
&nbsp;

&nbsp;
&nbsp;
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] )
        )
    )
)

Forum Discussion

Consecutive Days With Status Changes

2 Replies

Related content

Status Change

Changing Scorecard Status

Changing consecutive dates to from dates

periods of consecutive instances

DAX Count consecutive

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