topic Re: Finding Latest Date using Multiple Criteria with Multiple Entries for Unique IDs in DAX Commands and Tips

Finding Latest Date using Multiple Criteria with Multiple Entries for Unique IDs

dschul365 — Mon, 21 Nov 2022 20:07:07 GMT

Hello, I am looking for some assistance with finding the latest date for both On and Off times for each ID where there can be mulitple records of both Types for each ID.

Date	Type	ID	Latest On Time	Latest Off Time
1/1/22 12:15	on	1
1/2/22 12:30	on	1
1/4/22 16:15	off	1
1/2/22 18:15	off	1
1/5/22 12:15	on	2
1/6/22 12:15	off	2
1/7/22 12:15	on	2
1/8/22 12:15	on	3
1/9/22 12:15	on	3
1/10/22 12:15	on	4
1/11/22 12:15	off	4

Re: Finding Latest Date using Multiple Criteria with Multiple Entries for Unique IDs

tamerj1 — Mon, 21 Nov 2022 19:46:09 GMT

Hi dschul365

please try

Latest On Time =
MAXX (
FILTER (
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
'Table'[Type] = "on"
),
'Table'[Date]
)

Re: Finding Latest Date using Multiple Criteria with Multiple Entries for Unique IDs

dschul365 — Mon, 21 Nov 2022 20:09:27 GMT

Hello tamerj1

Unfortunately, once applied this formula only returns blank values within the Latest On Time column. Could you explain how the ALLEXCEPT forumla is intened to work here?

Re: Finding Latest Date using Multiple Criteria with Multiple Entries for Unique IDs

tamerj1 — Mon, 21 Nov 2022 20:20:09 GMT

dschul365

The CALCULATETABLE with ALLEXCEPT should return the complete table of the current ID. This should work if Type is a column rather than a measure. Is it?

aslo please try

Latest On Time =
MAXX (
FILTER (
CALCULATETABLE ( 'Table', REMOVEFILTERS (), VALUES ( 'Table'[ID] ) ),
'Table'[Type] = "on"
),
'Table'[Date]
)

Re: Finding Latest Date using Multiple Criteria with Multiple Entries for Unique IDs

dschul365 — Mon, 21 Nov 2022 20:25:41 GMT

You are correct, the latest date values are now populating. Thank you for the assist!