Who was assigned when booking confirmed

Hi LaurenceSD ,

Thanks for reaching out to the Microsoft fabric community forum. 

Perform a lookup that finds the latest AuditDateTime before the EventDateTime for each booking. This can be achieved using DAX. Here's how to approach it in DAX:

Step-by-Step in DAX:

1. Ensure relationships are set: Event Dates[BookId] → Audit Dates[BookId] (many-to-many or many-to-one depending on your model)

2. Create a calculated column on Event Dates table: This will find the TE (the assigned person) with the most recent AuditDateTime before the EventDateTime for that booking:

DAX

AssignedTEAtConfirmation =
VAR currentBookId = 'Event Dates'[BookId]
VAR currentEventTime = 'Event Dates'[EventDateTime]
RETURN
CALCULATE (
MAX('Audit Dates'[TE]),
FILTER (
'Audit Dates',
'Audit Dates'[BookId] = currentBookId &&
'Audit Dates'[AuditDateTime] <= currentEventTime
),
TOPN (
1,
FILTER (
'Audit Dates',
'Audit Dates'[BookId] = currentBookId &&
'Audit Dates'[AuditDateTime] <= currentEventTime
),
'Audit Dates'[AuditDateTime],
DESC
)
)

This logic filters the Audit Dates table to only the same BookId and where the AuditDateTime is less than or equal to the EventDateTime. Then it finds the latest TOPN 1 and gets the TE value.

Note: This method works efficiently on larger datasets. If you want to display the ConsultantId or other metadata, you can follow the same logic replacing MAX('Audit Dates'[TE]) with the field you need. If your data model isn’t performing well, consider optimizing relationships or pre-aggregating in Power Query.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you