Using DAX How to get next value of a row in a column to new column

[Anonymous]

Hi All,

Can you please help me to create Next State change Date and Next Work Item Id   columns for the below sample data please.

Work Item Id	State	State Change Date	Next State Change Date	Next Work Item Id
1365418	New	4/30/2022 3:58	5/3/2022 3:20	1365418
1365418	In Refinement	5/3/2022 3:20	5/3/2022 3:20	1365418
1365418	In Refinement	5/3/2022 3:20	5/25/2022 5:58	1365418
1365418	Ready for Dev	5/25/2022 5:58	6/3/2022 4:15	1365418
1365418	Active	6/3/2022 4:15	6/29/2022 8:26	1365418
1365418	Testing	6/29/2022 8:26	7/6/2022 9:39	1365420
1365420	Active	7/6/2022 9:39	7/20/2022 7:58	1365420
1365420	Testing	7/20/2022 7:58	8/10/2022 3:29	1365420
1365420	Accepted	8/10/2022 3:29	8/10/2022 3:29	1365420
1365420	Accepted	8/10/2022 3:29	8/18/2022 8:37	1365420
1365420	Closed	8/18/2022 8:37	4/30/2022 3:58	1365423

@jdbuchanan71 

Thanks,

[Anonymous]

I'm still looking for help on this. Please help

[Anonymous]

Hi @Anonymous,

You can try to use the following measure formulas if they suitable for your requirement:

Next State Change Date = 
VAR currDate =
    MAX ( T1[State Change Date] )
RETURN
    CALCULATE (
        MIN ( T1[State Change Date] ),
        FILTER ( ALLSELECTED ( T1 ), [State Change Date] > currDate )
    )

Next Work Item Id = 
VAR currDate =
    MAX ( T1[State Change Date] )
VAR nextDate =
    CALCULATE (
        MIN ( T1[State Change Date] ),
        FILTER ( ALLSELECTED ( T1 ), [State Change Date] > currDate )
    )
RETURN
    MINX (
        FILTER ( ALLSELECTED ( T1 ), [State Change Date] = nextDate ),
        [Work Item Id]
    )

Regards,
Xiaoxin Sheng

[Anonymous]

Hi @Anonymous , 
The logic which was shared is giving me no values , may be something is missing and I am trying to investigate . 

As the grouping is at the "work item id " for every workitem id it should calculate "next state change date"  and "next work item id".
The above sample is exaclty i have and I have to showcase these columns additionaly for me to calcualate the Aging of days foe each state. Thanks

[Anonymous]

Hi @Anonymous ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng