Forum Discussion

rogerthat's avatar
rogerthat
Helper I
3 years ago
Solved

Consecutive Days With Status Changes

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

IDDateSTATUSCONSECUTIVE  DAYS
XYZ1/1/2023COMPLETE1
BXY1/1/2023COMPLETE1
XYZ1/2/2023COMPLETE2
BXY1/2/2023COMPLETE2
XYZ1/5/2023INCOMPLETE1
XYZ1/6/2023INCOMPLETE2

 

I do have an Index.

"Consecutive"
"DAX"
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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] )
        )
    )
)

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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] )
        )
    )
)