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. 

 

 

 

 

 

 

 

 

 

 

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'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 )

6 Replies

  • 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 )
    • TSI's avatar
      TSI
      Advocate I
      7 years ago

      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 

    • yfquirogah's avatar
      yfquirogah
      Helper I
      6 years ago

      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.

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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 DateDanone IDSalary GradePrev Salary Grade
      Monday, 31 December 20181236B6A
      Thursday, 31 January 20191236B6A
      Thursday, 28 February 20191236B6A
      Sunday, 31 March 20191236B6A
      Tuesday, 30 April 20191236B6A
      Friday, 31 May 20191236B6A
      Sunday, 30 June 20191236B6A
      Wednesday, 31 July 20191236B6A
      Saturday, 31 August 20191236B6A
      Monday, 30 September 20191236B6A
      Thursday, 31 October 20191236A6A
      Saturday, 30 November 20191236A6A
      Tuesday, 31 December 20191236A6A
      Friday, 31 January 20201236A6A
      Saturday, 29 February 20201236A6A
      Tuesday, 31 March 20201236A6A
      Thursday, 30 April 20201236A6A
      Sunday, 31 May 20201236A6A

       

      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!!
  • parry2k's avatar
    parry2k
    Super User
    4 years ago

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