How to flag status change

[deanbland]

Hi, 

I have a dataset like this: 

ID	Date	Status
ID1	22/08/2020	Open
ID2	22/08/2020	Open
ID3	22/08/2020
ID1	22/09/2020	Closed
ID2	22/09/2020	Open

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!

[mahoneypat]

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]

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

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. 

Date	ID	Status	Status Change
28/06/2021	RID17	Open	0
05/07/2021	RID17	Closed	1
02/08/2021	RID17	Closed	0

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 · Accepted solution]

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.