New Column measure to map text values from previous month within same table

[ssk_1984]

Help to create new column measure to map values from previous month values based on employee ID from the same table.

Sample 

ID	Month	Met Status	Current Month-1 Met Status	Current Month-2 Met Status
A1	6/1/2025	Met
A2	6/1/2025	Met
A3	5/1/2025	Not Met
A1	5/1/2025	Met
A2	4/1/2025	Met
A3	4/1/2025	Met

in the above data, i have map  to 'Current Month-1 Met Status ' based on ID, 

Ex. ID A1, and Month 6/1/2025, Current status, Met, for Current Month-1, it has fetch details from the ID A1 last appeared month, A1 previous month status also, MET. That value to appeared on respective column

ID	Month	Met Status	Current Month-1 Met Status	Current Month-2 Met Status
A1	6/1/2025	Met	Met	Blank
A2	6/1/2025	Met	Blank	Met
A3	5/1/2025	Not Met
A1	5/1/2025	Met
A2	4/1/2025	Met
A3	4/1/2025	Met

ID	Month	Met Status	Current Month-1 Met Status	Current Month-2 Met Status
A1	6/1/2025	Met	Met	Blank
A2	6/1/2025	Met	Blank	Met
A3	5/1/2025	Not Met	Met	Met
A1	5/1/2025	Met	Blank	Blank
A2	4/1/2025	Met	Blank	Blank
A3	4/1/2025	Met	Blank	Blank

i have tried with  below measure but it is giving me proper values

[mark_endicott · Accepted solution]

@ssk_1984 

Create a date table with the code below:

DateTable = CALENDARAUTO()

You will need to create a relationship between this table and your Month column. 

Then create a measure with this code:

VAR _id = SELECTEDVALUE( Table[ID] )

RETURN
CALCULATE( SELECTEDVALUE( Table[Met Status] ), DATEADD( DateTable[Date], -1, MONTH ), KEEPFILTERS( Table[ID] = _id ))

Just replace Table with your table name. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[v-pagayam-msft]

Hi @ssk_1984 ,
Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.

[v-pagayam-msft]

Hi @ssk_1984 ,

I wanted to follow up to see if you had a chance to review the previous response provided by @mdaatifraza5556 and @mark_endicott . I hope it was helpful. If yes, please Accept the answer so that it will be helpful to others to find it quickly.

Thank you.

[v-pagayam-msft]

Hi @ssk_1984 ,
Thank you for the helpful response @mdaatifraza5556 and @mark_endicott !
I wanted to check in on your situation regarding new column measure to map text values from previous month within same table. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.
Thank you.

[mdaatifraza5556 · Accepted solution]

Hi @ssk_1984 

Create calculated column using below dax.

Current Month-1 Met Status =

VAR CurrentID = 'Table'[ID]

VAR CurrentMonth = 'Table'[Month]

VAR PrevMonth = EDATE(CurrentMonth, -1)

RETURN

    CALCULATE (

        MAX ('Table'[Met Status]),

        FILTER (

            'Table',

            'Table'[ID] = CurrentID &&

            'Table'[Month] = PrevMonth

        )

    )


second calculated col 

Current Month-2 Met Status =

VAR CurrentID = 'Table'[ID]

VAR CurrentMonth = 'Table'[Month]

VAR PrevMonth = EDATE(CurrentMonth, -2)

RETURN

    CALCULATE (

        MAX ('Table'[Met Status]),

        FILTER (

            'Table',

            'Table'[ID] = CurrentID &&

            'Table'[Month] = PrevMonth

        )

    )
If this answers your questions, kindly accept it as a solution and give kudos.

[mark_endicott · Accepted solution]

@ssk_1984 

Create a date table with the code below:

DateTable = CALENDARAUTO()

You will need to create a relationship between this table and your Month column. 

Then create a measure with this code:

VAR _id = SELECTEDVALUE( Table[ID] )

RETURN
CALCULATE( SELECTEDVALUE( Table[Met Status] ), DATEADD( DateTable[Date], -1, MONTH ), KEEPFILTERS( Table[ID] = _id ))

Just replace Table with your table name. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!