Filter by status change date

[hawkwolfe]

I am working with a file containing event data associated with agents, and have recently gotten access to a second table containing agent hierarchy data. With this hierarchy, I will now need to provide a filter to exclude agent managers from the data. The issue I'm trying to avoid is that agents are very often promoted to managers, so in a given date range, I want the filter to exclude the dates when they were a manager but include dates when they were an agent. Below are sample tables with the basic structure I'm working with.

Event table:

Hierarchy table, where "Date" is the last day of the associated status (updates daily):

In this sample file, applying a filter to retrieve only agent conversations from 1/1/2022 through 9/6/2022, I would expect the following result:

Bob's call on 1/4/2022 is excluded because it was his first day as a manager, and Janet's call is excluded because she has always been a manager.

I might be missing something obvious, but I haven't been able to find a way to filter in this way, and even creating the relationships between the tables is problematic because the hierarchy table has multiple entries for agent name. Any thoughts?

[v-easonf-msft · Accepted solution]

Hi， @hawkwolfe 

Try calculated columns:

date_agent = LOOKUPVALUE('Hierarchy table'[Date],'Hierarchy table'[Name],'call agent date'[agent],'Hierarchy table'[Status],"agent") 

filter = IF('call agent date'[date]<='call agent date'[date_agent],1,0)

Then apply the filter to visual filter pane.

Best Regards,
Community Support Team _ Eason