Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
42 | |
37 | |
22 | |
22 | |
21 |