Previous month value, not numerical

[yfquirogah]

I'm using the example another person posted but I'm looking for a different solutions.

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

Thank you!"

The solution given in the post is using a measure, however, I need it as a new column on the table I'm using, not as a measure, so basically, using the example it would be a new column with the paygrade the employee had the previous month. Does anyone know how it could be done? 

Thank you in advance for your help.

[Greg_Deckler · Accepted solution]

@yfquirogah - Perhaps:

Previous Month =
  VAR __EmployeeID = [EmployeeID]
  VAR __CurrentDate = [Calendar Date]
  VAR __PreviousDate = MAXX(FILTER('Table',[Employee ID] = __EmployeeID && [Calendar Date] < __CurrentDate),[Calendar Date])
RETURN
  MAXX(FILTER('Table',[Employee ID] = __EmployeeID && [Calendar Date] = __PreviousDate),[Pay Grade])

[az38 · Accepted solution]

Hi @yfquirogah 

try column

CALCULATE(LASTNONBLANK(Table[Pay Grade], 1), FILTER(ALL(Table), Table[Employee ID] = EARLIER(Table[Employee ID]) && Table[Calendar Date] = DATEADD(EARLIER(Table[Calendar Date]), -1, MONTH) ) )

[Greg_Deckler · Accepted solution]

@yfquirogah - Perhaps:

Previous Month =
  VAR __EmployeeID = [EmployeeID]
  VAR __CurrentDate = [Calendar Date]
  VAR __PreviousDate = MAXX(FILTER('Table',[Employee ID] = __EmployeeID && [Calendar Date] < __CurrentDate),[Calendar Date])
RETURN
  MAXX(FILTER('Table',[Employee ID] = __EmployeeID && [Calendar Date] = __PreviousDate),[Pay Grade])

[yfquirogah]

Wonderful! It worked perfectly. I haven't learned to use varibles in Power BI yet but will definitely look into it, it was a lot easier than what I had tried so far.

Thank you!

[Greg_Deckler]

@yfquirogah - Yes, variables are a must in my opinion, they break calculations down into manageable pieces, make the code more readable and enable troubleshooting. Highly recommended!