Forum Discussion
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. ...
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 )
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 ) How would you do this if it was needed as a new column and not a measure? If I need a new column with the value each ID had the previous month what would be the best way to do it? The value is not numerical.
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] )))RETURNCALCULATE( 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!!
