Getting the next status to current column

[aj1107]

Hello,

I need to check next status(st)=open for an id and then update the current row with a flag=A. I tried with earlier function but unable to get the expected result. Please suggest how to implement using DAX.

id,st,cdate,flag

1,st,jan17,A

1,open,feb17,NULL

Thank you

[prateekraina]

Hi @aj1107,

What happens to the previous row? Should the flag be changed to NULL from A in that?

Prateek Raina

[aj1107]

Hi

I've added few more records. whenever next st is open for the ID set then update current row as A. if the next st is <>open or no more records for the id Set (last row of each id) then NULL. 

id,st,cdate

1,close,jan17,A

1,open,feb17,NULL

1,close,mar17,A

1,open,apr17,NULL

2,st,jan17,NULL

3,st,jan17,A

3,open,Feb17,NULL

[prateekraina]

Hi @aj1107,

How about below DAX:

Flag = IF(Table1[St] = "Open","NULL","A")

Gives below result:

Does it work for you?

Prateek Raina

[aj1107]

My bad. I missed the logic . Actually in flag column i need to bring the next status to current row.(similar to lead function in sql) Please find below.

id,st,cdate

1,open,jan17,Open

1,open,feb17,open

1,open,mar17,close

1,close,apr17,NULL

2,st,jan17,NULL

3,open,jan17,close

3,close,Feb17,NULL

[v-sihou-msft · Accepted solution]

@aj1107

In this scenario, you can create a measure for next date first.

NextDate =
CALCULATE (
    MIN ( 'Table'[Date] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Id] ),
        'Table'[Date] > MAX ( 'Table'[Date] )
    )
)

Then create a calculated column to lookup the corresponding Status based on NextDate.

NextStatus =
LOOKUPVALUE (
    'Table'[Status],
    'Table'[Date], [NextDate],
    'Table'[Id], 'Table'[Id]
)

Regards,