topic Re: Previous Month Value (return Text) in DAX Commands and Tips

Previous Month Value (return Text)

TSI — Wed, 26 Jun 2019 09:38:36 GMT

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

Re: Previous Month Value (return Text)

parry2k — Wed, 26 Jun 2019 12:18:55 GMT

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 )

Re: Previous Month Value (return Text)

TSI — Wed, 26 Jun 2019 13:28:07 GMT

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

Re: Previous Month Value (return Text)

yfquirogah — Thu, 28 May 2020 03:46:53 GMT

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.

Re: Previous Month Value (return Text)

Anonymous — Fri, 26 Jun 2020 23:00:43 GMT

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

Re: Previous Month Value (return Text)

_Kevin_ — Wed, 11 May 2022 14:54:27 GMT

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

Re: Previous Month Value (return Text)

parry2k — Wed, 11 May 2022 15:46:05 GMT

_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.