Forum Discussion
Power Query Max Date for Multiple User IDs
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!
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)
3 Replies
- HotChilli
Community Champion
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)
- 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])) 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.