topic Re: find previous value in a snapshot table in DAX Commands and Tips

find previous value in a snapshot table

coollehavre — Sat, 21 Sep 2024 06:19:07 GMT

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

Re: find previous value in a snapshot table

Jihwan_Kim — Sat, 21 Sep 2024 07:25:04 GMT

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] )

Re: find previous value in a snapshot table

ThxAlot — Sat, 21 Sep 2024 09:21:34 GMT

Re: find previous value in a snapshot table

dharmendars007 — Sat, 21 Sep 2024 10:38:05 GMT

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