Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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])
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) ) )
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) ) )
@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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |