Previous Month Value (return Text)

[TSI]

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. 

So, example for Employee ID 100, for 1/1/2019, it would show F.

Would anyone know how to do this? Most reference material show SUMX calculations for previous month's numbers, but not for text. 

Thank you!

Best regards,

Kim

[parry2k]

@_Kevin_ Thanks for the feedback. Glad you find the solution useful for your use case. 

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[parry2k · Accepted solution]

@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_]

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

[Anonymous]

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!!

[yfquirogah]

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.

[TSI]

Hi @parry2k 

Thank you so much for the solution, it worked like a charm.

Appreciate the Measure, it saved me from creating 4 columns! (that's what it took when I tried to use Lookupvalue).

Your measure is definitely superior.

Best regards,

Kim