Power Query Max Date for Multiple User IDs

[Anonymous]

Hello all,

I am looking for help for what was a very easy thing to deliver as a custom column using a simple MAX statement. Here is some test data. Essentially I need to figure out how to build the Maxdate column below in Power Query in order to merge it with a terminated associate data set. 

Effective Date	Associate ID	Maxdate
3/31/2022	2005	3/31/2022
2/28/2022	2005	3/31/2022
1/31/2022	2005	3/31/2022
3/31/2022	2006	3/31/2022
2/28/2022	2006	3/31/2022
1/31/2022	2006	3/31/2022
11/30/2021	3000	11/30/2021
10/31/2021	3000	11/30/2021
9/30/2021	3000	11/30/2021

I imagine it will be something similar to this article, but I am still a novice, and can't figure out to how do it for each specific user ID.

Thanks in advance!

[HotChilli]

I need to combat this whenever I see it.

It's not good practice to base a calculated column on a measure.  The column gets a value when data is loaded or refreshed.

A measure is intended for visualisations. It evaluates depending on the context it is used in.

You will get a value when a column is based on a measure but it might not be what you hope for (so it shouldn't be used this way).

If you want a DAX column it will be :

CALCULATE( MAX(active[Effective Date]),ALLEXCEPT(active, active[Associate ID]))

[Anonymous]

Appreciate the answer! For those looking, I was able to do this another way via measures and custom columns. Note: the tables are linked on associate ID.

1. Create a measure for max date from one table: 

Test MaxDate = MAX('active'[Report Effective Date])

2. Using a custom column on the termianted table, create a new column, and point it to the new measure: 

MaxDateValidation = [Test MaxDate]

This pulled in the maximum that the associate ID appeared on my active table. I was then able to do a simple IF custom column to detect if an associate was a rehire or not.

[HotChilli · Accepted solution]

Duplicate the table.

Perform a 'Group By' on ID with Max (Effective Date)

----

Use Merge Queries to join the resulting table with the original table ( joining on ID)