find previous value in a snapshot table

[coollehavre]

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 

[Jihwan_Kim · Accepted 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]
    )

[ThxAlot · Accepted solution]

[dharmendars007 · Accepted solution]

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

LinkedIN 

[ThxAlot · Accepted solution]

[Jihwan_Kim · Accepted 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]
    )