How to lookup and compare data in the same table

[char23]

Hello, I need help with adding a new column. Is there a way to lookup values/compare data in the same table? I have schedule data that will be added to the table for each month. Most values in the ID column will repeat each month, and there will be some IDs that are removed or added. I am trying to add a new column to the same table that shows the status of the previous month. Columns Status and Schedule Status are calculated columns with Dax. Any help to put me in the right directions is useful. 

ID	Status	Schedule Status	Previous Status
ABC	On time	Previous
DEF	Not on Time	Previous
GHI	Future	Previous
JKL	On time	Previous
ABC	On time	Current	On time
DEF	On time	Current	Not on Time
GHI	On time	Current	Future
JKL	On time	Current	On time
MNO	On time	Current	Added

[samratpbi · Accepted solution]

Hi, I created the below table:

Then created the below measure:

Prev Status =

VAR _selectedID = SELECTEDVALUE(PreviousStat[ID])

VAR _preVal =

CALCULATE

(

    MIN(PreviousStat[Schedule Status]),

    PreviousStat[ID] = _selectedID,

    ALLEXCEPT(PreviousStat, PreviousStat[Previous Status]),

    PreviousStat[Previous Status] <> "Current"        

)

RETURN

IF(

    SELECTEDVALUE(PreviousStat[Previous Status]) <> "Current",

    "",

    IF(

        ISBLANK(_preVal), "Added",

        _preVal

    )

)

And the output:

If this helps to resolve your problem, then please mark it as solution! Thanks

Dont forget to give Kudos 🙂

[char23]

Thank you for sending this. This seems to help a lot! However I am trying to alter this for my data and should have provided more details initially. My [Previous Status] column is actually a series of numbers where 1 = current, 2= previous, and >2 = old data that I want to ignore. I've tried altering your formula similar to what is below (red text below table), but I keep getting "Added" for every record in the new column. I want to add the column in green. Also, will eventually have to add to show deleted records (May need another column for this, i'm not sure) 

ID	Schedule Status	Previous Status	Prev Status (from Previous status =2)
ABC	Not Started	3
DEF	Not Started	3
GHI	Not Started	3
JKL	Not Started	3
ABC	On time	2
DEF	Not on Time	2	Deleted (Not found from Previous status =1)
GHI	Future	2
JKL	On time	2
ABC	On time	1	On time
GHI	On time	1	Future
JKL	On time	1	On time
MNO	On time	1	Added

Prev Status =

VAR _selectedID = SELECTEDVALUE(PreviousStat[ID])

VAR _preVal =

CALCULATE

(

    MIN(PreviousStat[Schedule Status]),

    PreviousStat[ID] = _selectedID,

    ALLEXCEPT(PreviousStat, PreviousStat[Previous Status]),

    PreviousStat[Previous Status] = 2        

)

RETURN

IF(

    SELECTEDVALUE(PreviousStat[Previous Status]) > 1,

    "",

    IF(

        ISBLANK(_preVal), "Added",

        _preVal

    )

)