Forum Discussion
TSI
Advocate I
Advocate IPrevious 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. ...
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
Super User
Super UserTSI 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 ) 
Hi there parry2k
I am trying to do the same thing as previous user but it's not working for me and can't work out why.
My data is below
'Prev salary grade' is the dax calculated column.
As you can see it should return 6B until the report date is 30 Nov 2019 and then should start returning 6A. Instead it always returns 6A.
| Report Date | Danone ID | Salary Grade | Prev Salary Grade |
| Monday, 31 December 2018 | 123 | 6B | 6A |
| Thursday, 31 January 2019 | 123 | 6B | 6A |
| Thursday, 28 February 2019 | 123 | 6B | 6A |
| Sunday, 31 March 2019 | 123 | 6B | 6A |
| Tuesday, 30 April 2019 | 123 | 6B | 6A |
| Friday, 31 May 2019 | 123 | 6B | 6A |
| Sunday, 30 June 2019 | 123 | 6B | 6A |
| Wednesday, 31 July 2019 | 123 | 6B | 6A |
| Saturday, 31 August 2019 | 123 | 6B | 6A |
| Monday, 30 September 2019 | 123 | 6B | 6A |
| Thursday, 31 October 2019 | 123 | 6A | 6A |
| Saturday, 30 November 2019 | 123 | 6A | 6A |
| Tuesday, 31 December 2019 | 123 | 6A | 6A |
| Friday, 31 January 2020 | 123 | 6A | 6A |
| Saturday, 29 February 2020 | 123 | 6A | 6A |
| Tuesday, 31 March 2020 | 123 | 6A | 6A |
| Thursday, 30 April 2020 | 123 | 6A | 6A |
| Sunday, 31 May 2020 | 123 | 6A | 6A |
Formula
Prev Salary Grade =
VAR __prevDate =
CALCULATE(
MAX( 'Promotions'[Report Date] ),
FILTER(
ALLEXCEPT( Promotions, Promotions[Danone ID] ),
Promotions[Report Date] < MAX( Promotions[Report Date] )
)
)
RETURN
CALCULATE( MAX( Promotions[Salary Grade] ), ALLEXCEPT( Promotions, Promotions[Danone ID]), Promotions[Report Date]= __prevDate )
Any help much appreciated - I have been going round and round in circles for longer than I care to admit!!