Check previous text (status) in column

[JMHenriques]

Hi everyone,

i have a historical table with the same ID from different weeks and want to check if the status column changed from one week to another.  I've sucefully added a rank column as well as correctly identifying the previous weekday. Nevertheless, my previous status column is not working correctly as showed below:

ID	Hiring_Status_Real	WeekDay	rank	Previous Week date	Previous Status
128	Closed	20/12/2021	6	13/12/2021 00:00	In Progress
128	Closed	13/12/2021	5	06/12/2021 00:00	In Progress
128	In Progress	06/12/2021	4	29/11/2021 00:00	In Progress
128	In Progress	29/11/2021	3	22/11/2021 00:00	In Progress
128	In Progress	22/11/2021	2	15/11/2021 00:00	In Progress
128	In Progress	15/11/2021	1

For the rank 6 the previous status should be "Closed" and not "In progress"...

My formulas:

rank = RANKX(FILTER(Table_Requested_Resources_Weekly_Dump, Table_Requested_Resources_Weekly_Dump[ID] = EARLIER(Table_Requested_Resources_Weekly_Dump[ID])), Table_Requested_Resources_Weekly_Dump[WeekDay],,ASC,Dense)

Previous Week date =

CALCULATE(

MAX(Table_Requested_Resources_Weekly_Dump[WeekDay]),

FILTER(

ALLEXCEPT(Table_Requested_Resources_Weekly_Dump,

Table_Requested_Resources_Weekly_Dump[ID]

),

Table_Requested_Resources_Weekly_Dump[WeekDay]

< EARLIER(Table_Requested_Resources_Weekly_Dump[WeekDay])

)

)

Previous Status =

CALCULATE(

MAX('vm-Requested_Resources'[Hiring_Status_Real]),

FILTER(

ALLEXCEPT(

Table_Requested_Resources_Weekly_Dump,

Table_Requested_Resources_Weekly_Dump[ID]

),

Table_Requested_Resources_Weekly_Dump[WeekDay]

< EARLIER(Table_Requested_Resources_Weekly_Dump[WeekDay]) && Table_Requested_Resources_Weekly_Dump[rank] = EARLIER(Table_Requested_Resources_Weekly_Dump[rank]) - 1

)

)

[amitchandak · Accepted solution]

@JMHenriques , Try a new column like

new column =
var _max = maxx(filter(Table, [ID] = earlier([ID]) && [WeekDay] < earlier([WeekDay])),[WeekDay])
return
maxx(filter(Table, [ID] = earlier([ID]) && [WeekDay] =_max),[Hiring_Status_Real])

If you want a measure then you should have seperate date/week table with rank and then try like

Last Week = CALCULATE(MAx('Table'[Hiring_Status_Real]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

[JMHenriques]

@amitchandak Many thanks for the solution. worked perfectly!