Lookup different value depending on date column

[Anonymous]

I have a table that shows date of communications actioned (Comms[creation date]), along with the name of the staff member that processed them (Comms[actioned by]). I also have another table showing staff members (Staff[first name]) and the team they work in (Staff[Team]), and a third table that shows the dates a team member was working in a particular team (RoleChange[role start] and RoleChange[role end]). Some staff have moved teams and so the RoleChange table will contain multiple entries for staff members, and no entries for team members that haven't moved.

I want to create a calculated column (I think!?) in the communications table that shows the team that the staff member who actioned the communication was working in at the time the communication was actioned. 

I've been assuming that I need some kind of LOOKUPVALUE but with variables relating to Comms[creation date] and RoleChange[role start] and RoleChange[role end], but I'm not managing to get anywhere.

This is what the relevant section of my model looks like:

Any help much appreciated!

[Stachu · Accepted solution]

this looks a lot like this problem, it has a solution in DAX and Power Query
https://community.powerbi.com/t5/Power-Query/Linking-TimeStamp-to-Value-between-Dates-in-Seperate-Ta...

have a read, and if something is not clear please post again

[Anonymous]

That's perfect, thanks very much for your help

---

@Stachu wrote:

this looks a lot like this problem, it has a solution in DAX and Power Query
https://community.powerbi.com/t5/Power-Query/Linking-TimeStamp-to-Value-between-Dates-in-Seperate-Ta...

have a read, and if something is not clear please post again

---