Previous row value to a New column

[Naveen112]

Hi All,

Can you please help me the below Output for the Previous_State Column : I need to have the previous value of  each state  to derive the "State time in days" So that it will be useful for the management to check how many days a ticket has been pending in each state to track the users.
Please note that State Column has duplicate states as there is a DATE column with current snap shot of dates till date. Sample Dat below a is the exact sample of what I have in database. As I am using STATE CHANGE DATE column for my calculation and not using the DATE column in any of my calculations I havent included this column in the below sample data.
Tried with the below code but not working:

Previous_State=

CALCULATE (

     MAX('table'[State]) ,

        ALLEXCEPT ( 'table', 'table'[Work Item Id] ),

        'table'[State] < EARLIER('table'[State])  

)

Workitem ID	State	State change Date	Previous_State	State Time In Days
1001	backlog	4/1/2024
1001	backlog	4/1/2024
1001	backlog	4/1/2024
1001	backlog	4/1/2024
1001	Inprogress	15/1/2024	backlog	11
1001	Inprogress	15/1/2024	backlog	11
1001	Inprogress	15/1/2024	backlog	11
1001	Done	20/1/2024	Inprogress	5
1001	Done	20/1/2024	Inprogress	5
1001	Done	20/1/2024	Inprogress	5
1001	Done	20/1/2024	Inprogress	5
2001	backlog	5/1/2024
2001	backlog	5/1/2024
2001	backlog	5/1/2024
2001	Inprogress	13/1/2024	backlog	8
2001	Inprogress	13/1/2024	backlog	8
2001	Inprogress	13/1/2024	backlog	8
2001	Done	18/1/2024	Inprogress	5
2001	Done	18/1/2024	Inprogress	5
2001	Done	18/1/2024	Inprogress	5

Thanks,

[ThxAlot]

[jdbuchanan71 · Accepted solution]

@Naveen112 

You need to look at the date, rather than the state, like this.

Previous State = 
VAR _PrevDate =
    CALCULATE (
        MAX ( 'Table'[State change Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Workitem ID] ),
        'Table'[State change Date] < EARLIER ( 'Table'[State change Date] )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[State] ),
        ALLEXCEPT ( 'Table', 'Table'[Workitem ID] ),
        'Table'[State change Date] = _PrevDate
    )

Then the Days in state is like this.

State Time In Days = 
VAR _PrevDate =
    CALCULATE (
        MAX ( 'Table'[State change Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Workitem ID] ),
        'Table'[State change Date] < EARLIER ( 'Table'[State change Date] )
    )
RETURN
    IF ( ISBLANK ( _PrevDate ), BLANK (), INT ( [State change Date] - _PrevDate ) )