Forum Discussion

JMHenriques's avatar
JMHenriques
Frequent Visitor
4 years ago
Solved

Check previous text (status) in column

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:

 

IDHiring_Status_RealWeekDayrankPrevious Week datePrevious Status
128Closed20/12/2021613/12/2021 00:00In Progress
128Closed13/12/2021506/12/2021 00:00In Progress
128In Progress06/12/2021429/11/2021 00:00In Progress
128In Progress29/11/2021322/11/2021 00:00In Progress
128In Progress22/11/2021215/11/2021 00:00In Progress
128In Progress15/11/20211  

 

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
)
)
 
 

 

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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))

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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))