Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
coollehavre
Frequent Visitor

find previous value in a snapshot table

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_idjob_idsnapshot_datejob_id_previous_month
E001J12331/01/2024 
E002J23031/01/2024 
E003J23031/01/2024 
E001J23528/02/2024J123
E002J23028/02/2024J230
E003J23028/02/2024J230

 

Any help to do this ? 

 

Thanks 

3 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1726903466838.png

 

 

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

ThxAlot
Super User
Super User

ThxAlot_0-1726910445556.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

dharmendars007
Solution Sage
Solution Sage

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

 

dharmendars007_0-1726915016338.png

 

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 

 

View solution in original post

3 REPLIES 3
dharmendars007
Solution Sage
Solution Sage

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

 

dharmendars007_0-1726915016338.png

 

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
Super User
Super User

ThxAlot_0-1726910445556.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1726903466838.png

 

 

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors