We were working with some HR data which included multiple date fields such as Hire Date, Transfer Date, Promotion Date, and Termination Date. We needed to determine the most recent event date before termination. We ended up using the MAXX function to do this.

Sample dataset to demonstrate the scenario:

Using the following dataset to demonstrate the example:

Employee	Hire Date	Transfer Date	Promotion Date	Termination Date
Alice	2020-01-15	2021-05-10	2022-03-20	2023-06-15
Bob	2019-11-01	2020-07-15	2021-10-05	(blank)
Carol	2021-03-25	(blank)	2021-09-14	2022-02-28

The goal is to calculate the most recent event and event date (i.e., the latest event and its date) between Hire Date, Transfer Date, and Promotion Date for each row. Termination Date was excluded from the comparison because the goal was to find the latest event before Termination (if that had occurred).

Using MAXX for Row-by-Row Evaluation

MAXX is an iterator function in DAX, meaning it evaluates an expression for each row of a table, then returns the maximum value. Iterator functions such as MAXX and SUMX work row-by-row over a table, in contrast to aggregate functions like MAX and SUM which operate over an entire column at once.

• Aggregate example (MAX): Finds the highest value in a column across all rows.
• Iterator example (MAXX): Evaluates an expression for each row in a virtual table, then finds the highest value.

This makes MAXX ideal for scenarios like this where the various dates are in multiple columns of the same row, and we need to find the max of these dates on each row.

DAX Code Example: This is an example of the code that was used to derive the latest event date.

Latest Event Date =
MAXX(
{
[Hire Date],
[Transfer Date],
[Promotion Date]
},
[Value]
)

Code Explanation:

1. Create a virtual table with one column and three rows—one for each date we want to consider.
2. MAXX iterates through this virtual table, evaluates [Value] (the date), and returns the latest / maximum (max) date for each iteration.

Expected Output based on the sample dataset:

Employee	Hire Date	Transfer Date	Promotion Date	Termination Date	Latest Event Date
Alice	2020-01-15	2021-05-10	2022-03-20	2023-06-15	2022-03-20
Bob	2019-11-01	2020-07-15	2021-10-05	(blank)	2021-10-05
Carol	2021-03-25	(blank)	2021-09-14	2022-02-28	2021-09-14

This is much cleaner than using nested IF checks to determine the latest date / latest event for each record. Of course, the MAXX function can be used in other scenarios where you want to find the max value across multiple columns on each row.

Thanks for reading and I hope you found this useful!