Forum Discussion

Werfuks's avatar
Werfuks
Frequent Visitor
2 years ago
Solved

How to get the Previous Month Value

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/YearEmployee IDStatusStatus PRV Month
30.06.20241188On Leave 
30.06.20241066On Leave 
30.06.20241100Active 
30.06.2024900Active 
30.06.20241155Active 
30.06.20241245Active 
30.06.20241056Active 
31.07.20241188ActiveOn Leave
31.07.20241066ActiveOn Leave
31.07.20241100On LeaveActive
31.07.2024900ActiveActive
31.07.20241155ActiveActive
31.07.20241245ActiveActive
31.07.20241056ActiveActive
31.08.20241188ActiveActive
31.08.20241066ActiveActive
31.08.20241100On LeaveOn Leave
31.08.2024900ActiveActive
31.08.20241155ActiveActive
31.08.20241245On LeaveActive
31.08.20241056ActiveActive

 

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

3 Replies

  • mark_endicott's avatar
    mark_endicott
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
    mark_endicott
    Icon for Super User rankSuper User
    2 years ago

    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.