Forum Discussion
Previous month value, not numerical
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.
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])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) ) )
4 Replies
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])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!
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!
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) ) )
