Forum Discussion

TSI's avatar
TSI
Advocate I
7 years ago
Solved

Previous Month Value (return Text)

Hi Community,   I have a table of employee IDs and their Pay Grades each month.  I'm trying to create a new column 'Previous Month' which would show me the Pay Grade from the month before.  ...
  • parry2k's avatar
    parry2k
    7 years ago

    TSI try following measure

     

    Prev Pay Grade = 
VAR __prevDate = 
CALCULATE( 
    MAX( 'Table'[Calendar Date] ), 
    FILTER( 
        ALLEXCEPT( 'Table', 'Table'[Employee ID] ),  
        'Table'[Calendar Date] < MAX( 'Table'[Calendar Date] )
    )
)
RETURN
    CALCULATE( MAX( 'Table'[Pay Grade] ), ALLEXCEPT( 'Table', 'Table'[Employee ID] ), 'Table'[Calendar Date] = __prevDate )
parry2k's avatar
parry2k
Super User
7 years ago

TSI try following measure

 

Prev Pay Grade = 
VAR __prevDate = 
CALCULATE( 
    MAX( 'Table'[Calendar Date] ), 
    FILTER( 
        ALLEXCEPT( 'Table', 'Table'[Employee ID] ),  
        'Table'[Calendar Date] < MAX( 'Table'[Calendar Date] )
    )
)
RETURN
    CALCULATE( MAX( 'Table'[Pay Grade] ), ALLEXCEPT( 'Table', 'Table'[Employee ID] ), 'Table'[Calendar Date] = __prevDate )
_Kevin_'s avatar
_Kevin_
Helper I
4 years ago

Thanks parry2k . I barely have any joy on this forum but you nailed three years ago what I needed urgently today. Great response.