MAX Date and 2nd Max Date Help!

[mattramirez2020]

Hello, I am trying to make two calculated columns in a query for max date and 2nd max date. Both of the dates come from another query and have the criteria of having been "completed". I am really struggling to figure out how to do this in a calculated column and would really appreciate any help. Any help would be immensely appreciated!!!!

Query: AllPIDs

PeopleID

1

2

3

Query: Appointments

PeopleID	Date	Status
1	1/1/20	Completed
1	2/1/20	Completed
1	4/1/20	Completed
1	6/20/20	Scheduled
2	12/1/18	Completed
2	12/5/18	Completed
3	12/31/19	Completed

Ideal Query: AllPIDs with two new calculated columns referencing Appointments Table

PeopleID	Max Completed Date	2nd Max Completed Date
1	4/1/20	2/1/20
2	12/5/18	12/1/18
3	12/31/19

[mahoneypat]

Please try these expressions in your columns.  This assumes you have a 1:many relationship between your tables.

Max Date = CALCULATE(Max(Appointments[Date]), Appointments[Status] = "Completed")

2nd Max Date = var __maxdate = [Max Date]

return CALCULATE(Max(Appointments[Date]), Appointments[Status] = "Completed", Appointments[Date] <> __maxdate)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[mattramirez2020]

Hey @mahoneypat ,

It tells me that this name is not recognized? Limitation of Custom Column possibly?

[mahoneypat · Accepted solution]

This is more easily solved with DAX than in the query editor (M).  The expressions I provided are for DAX calculated columns, not custom columns in the query editor.  Unless you need to do data transformations involving these new columns, it is the way to go.  Load your query, and then click on New Column for each of them.

Regards,

Pat

[mattramirez2020]

Worked perfectly! Thanks!