Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi everyone,
i have a snapshot table (same employees every month in a single table) and i need to compare job_id for an employee from previous month
i would like to create a calculated column or measure (job_id_previous_month) like this :
employee_id | job_id | snapshot_date | job_id_previous_month |
E001 | J123 | 31/01/2024 | |
E002 | J230 | 31/01/2024 | |
E003 | J230 | 31/01/2024 | |
E001 | J235 | 28/02/2024 | J123 |
E002 | J230 | 28/02/2024 | J230 |
E003 | J230 | 28/02/2024 | J230 |
Any help to do this ?
Thanks
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
job_id_previous_month CC =
VAR _currentrowemployee = data[employee_id]
VAR _currentrowmonth = data[snapshot_date]
RETURN
MAXX (
FILTER (
data,
data[employee_id] = _currentrowemployee
&& data[snapshot_date] = EOMONTH ( _currentrowmonth, -1 )
),
data[job_id]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Hello @coollehavre ,
Please try the below code..
job_id_previous_month =
VAR currentEmployee = 'Table'[employee_id]
VAR currentDate = 'Table'[snapshot_date]
RETURN
CALCULATE(MAX('Table'[job_id]),FILTER('Table','Table'[employee_id] = currentEmployee &&
EOMONTH('Table'[snapshot_date], 0) = EOMONTH(currentDate, -1)))
If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!
Thank You
Dharmendar S
Hello @coollehavre ,
Please try the below code..
job_id_previous_month =
VAR currentEmployee = 'Table'[employee_id]
VAR currentDate = 'Table'[snapshot_date]
RETURN
CALCULATE(MAX('Table'[job_id]),FILTER('Table','Table'[employee_id] = currentEmployee &&
EOMONTH('Table'[snapshot_date], 0) = EOMONTH(currentDate, -1)))
If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!
Thank You
Dharmendar S
Hi,
Please check the below picture and the attached pbix file.
job_id_previous_month CC =
VAR _currentrowemployee = data[employee_id]
VAR _currentrowmonth = data[snapshot_date]
RETURN
MAXX (
FILTER (
data,
data[employee_id] = _currentrowemployee
&& data[snapshot_date] = EOMONTH ( _currentrowmonth, -1 )
),
data[job_id]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
15 | |
14 | |
12 | |
9 |