How to get the Previous Month Value

[Werfuks]

Hi Community, I'm trying to get the value from the previous month in the following table, which is about trying to see if there was a change in the employee status (employee is "active" or "on leave"). Does anyone know how to get it? I already found some ideas in older chats but I didn't manage to make it work.

Thank you in advance!

Month/Year	Employee ID	Status	Status PRV Month
30.06.2024	1188	On Leave
30.06.2024	1066	On Leave
30.06.2024	1100	Active
30.06.2024	900	Active
30.06.2024	1155	Active
30.06.2024	1245	Active
30.06.2024	1056	Active
31.07.2024	1188	Active	On Leave
31.07.2024	1066	Active	On Leave
31.07.2024	1100	On Leave	Active
31.07.2024	900	Active	Active
31.07.2024	1155	Active	Active
31.07.2024	1245	Active	Active
31.07.2024	1056	Active	Active
31.08.2024	1188	Active	Active
31.08.2024	1066	Active	Active
31.08.2024	1100	On Leave	On Leave
31.08.2024	900	Active	Active
31.08.2024	1155	Active	Active
31.08.2024	1245	On Leave	Active
31.08.2024	1056	Active	Active

I already tried this without success:

Measure1:

Date = DATEADD(Basisdaten_Headcount[Month/Year],-1,MONTH)

 Measure2:

PRV Status =
VAR __Date = [Date]
VAR __PreviousDate = MAXX( FILTER(Basisdaten_Headcount,[Employee ID] = [Employee ID] && [Date] < __Date),[Date])
VAR __PreviousStatus = MAXX( FILTER(Basisdaten_Headcount,[Employee ID] = [Employee ID] && [Date] = __Date), [Currently Active])
RETURN
__PreviousStatus

[ThxAlot · Accepted solution]

[mark_endicott]

@Werfuks - It looks like you have not created a date table, which is essential for doing time intelligence calculations. You can build a simple one using CALENDARAUTO() when creating a calculated table. There is also a great video of options here: https://www.youtube.com/watch?v=WybnTHDl-AM

Once you have done this, make sure you create a relationship between your new date table date column and the date column in your Basisdaten_Headcount table. 

You can then use the following DAX to create the measure - you will need to change names to suit your model:

CALCULATE( [Measure], DATEADD('Date Table'[Date Column],-1,MONTH)

If this works for you, pelase accept it as the solution for others - it helps with visibility.

[mark_endicott]

@Werfuks - It looks like you have not created a date table, which is essential for doing time intelligence calculations. You can build a simple one using CALENDARAUTO() when creating a calculated table. There is also a great video of options here: https://www.youtube.com/watch?v=WybnTHDl-AM

Once you have done this, make sure you create a relationship between your new date table date column and the date column in your Basisdaten_Headcount table. 

You can then use the following DAX to create the measure - you will need to change names to suit your model:

CALCULATE( [Measure], DATEADD('Date Table'[Date Column],-1,MONTH)

If this works for you, pelase accept it as the solution for others - it helps with visibility.