Forum Discussion

deanbland's avatar
deanbland
Helper III
5 years ago
Solved

How to flag status change

Hi, 

 

I have a dataset like this: 

 

IDDateStatus
ID122/08/2020Open
ID222/08/2020Open
ID322/08/2020 
ID122/09/2020Closed
ID222/09/2020Open

 

I am wanting to create a measure or calculated column that assesses this change, and assigns a 1 to where the status has changed, per month. Some ID's haven't been assigned a status so there are three variables in this (null, Open, Closed) . 

 

I have tried a couple of ways but they always calculate the blank status as a change ! 

 

Any help is appreciated - thanks!

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi deanbland ,

     

    Please try the following formula:

     

    Column = 
var PreDate = 
    MAXX(
        FILTER(
            'Table',
            'Table'[Date] < EARLIER('Table'[Date])
            && 'Table'[ID] = EARLIER('Table'[ID])
        ),
        'Table'[Date]
    )
var PD_Status = 
    CALCULATE(
        MAX('Table'[Status]),
        FILTER(
            'Table',
            'Table'[Date] = PreDate 
            && 'Table'[ID] = EARLIER('Table'[ID])
        )
    )
return 
    IF( PD_Status = BLANK() || PD_Status = 'Table'[Status], 0, 1 )

     

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Worked on this at same time.  Here is a measure expression you can try too.

     

     

     

    StatusChange =
    VAR latestdate =
        MAX ( 'Status'[Date] )
    VAR lateststatus =
        CALCULATE ( MIN ( 'Status'[Status] ), 'Status'[Date] = latestdate )
    VAR prevdate =
        CALCULATE ( MAX ( 'Status'[Date] ), 'Status'[Date] < latestdate )
    VAR prevstatus =
        CALCULATE ( MIN ( 'Status'[Status] ), 'Status'[Date] = prevdate )
    RETURN
        IF ( ISBLANK ( prevstatus ) || prevstatus = lateststatus, "N", "Y" )

     

    Pat

     

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    deanbland 

    I added a new column, also add one more record to check the scenario in the sample data.

    Change = 
INT(
    COUNTROWS(
        FILTER(
            Table2,
            Table2[ID] = EARLIER(Table2[ID]) && FORMAT(Table2[Date],"yymm") = FORMAT(EARLIER(Table2[Date]),"yymm") && Table2[Status] <> BLANK()
        )
    )>1
)

     



    • deanbland's avatar
      deanbland
      Helper III
      5 years ago

      Hi Fowmy , 

       

      I have tried your suggestion and it isn't picking up the status changes. The highlighted column on the right shows the output from the DAX you provided, which is not the expected outcome as the status change from Open to Close should have been marked. 

       

       

      DateIDStatusStatus Change
      28/06/2021RID17Open0
      05/07/2021RID17Closed1
      02/08/2021RID17Closed0

       

      Above shows an exact cut from the data, only including the columns needed and filtered on the example in the picture. The end column is the expected output. How would I go about editing your DAX to provide this outcome? 

       

      Thanks for all your help so far!

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        5 years ago

        Hi deanbland ,

         

        Please try the following formula:

         

        Column = 
var PreDate = 
    MAXX(
        FILTER(
            'Table',
            'Table'[Date] < EARLIER('Table'[Date])
            && 'Table'[ID] = EARLIER('Table'[ID])
        ),
        'Table'[Date]
    )
var PD_Status = 
    CALCULATE(
        MAX('Table'[Status]),
        FILTER(
            'Table',
            'Table'[Date] = PreDate 
            && 'Table'[ID] = EARLIER('Table'[ID])
        )
    )
return 
    IF( PD_Status = BLANK() || PD_Status = 'Table'[Status], 0, 1 )

         

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

        Best Regards,
        Winniz

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.