Forum Discussion

Birdjo's avatar
Birdjo
Resolver II
9 years ago

Filter Report by Max Date

Hello,

I am working on a human resources report where I have to use the different positions. But when our employees edit the positions new row appears, while I only need the latest version of the records. 
I guess the Power Query can be modified to show only latest date per record but I don't know how.


As you can see there are duplicate records. I would like to have the distinct values for WORKER while the MODIFIEDDATETIME is the latest.

Thank you in advance!

Kind Regards,
Birdjo

Need Help

6 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi Birdjo

    Here is a measure that might work

     

    Last Worker = CALCULATE
				(MAX('Table3'[WORKER]),
				FILTER(
					'Table3',
					[MODIFIEDDATETIME]=MAX('Table3'[MODIFIEDDATETIME])
					)
				)

    Or this will give you a 1 line table that you can build on

    New Table = 
VAR 
    LastModifiedDate = MAX('Table3'[MODIFIEDDATETIME])
RETURN 
    FILTER(
        'Table3',
        'Table3'[MODIFIEDDATETIME]=LastModifiedDate
        )

     

  • slava_k's avatar
    slava_k
    Regular Visitor
    9 years ago

    You can use the "Group By" function  in Query Editor to get maxinum date by ID. If you need to keep the position field - you can load the table again and make inner join by ID and maximum date to grouped table.

    • Birdjo's avatar
      Birdjo
      Resolver II
      9 years ago

      I think a measure wont work. I need a table with the distinct values for WORKER where they are the latest by MODIFIEDDATE.


      I've tried "Group By" Function and I got it the way I need, but when I add the other columns like "Personnel Number" and "Department" it becomes the same again.

       

      • slava_k's avatar
        slava_k
        Regular Visitor
        9 years ago

        Don't add the other columns, load the table again so you have 2 tables: 1 - agregated table with ID and max date, 2 - regular table with all the columns. Then join it with inner join by ID and max date.