topic Re: Lookup different value depending on date column in DAX Commands and Tips

Lookup different value depending on date column

Anonymous — Fri, 16 Aug 2019 15:42:37 GMT

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!

Re: Lookup different value depending on date column

Stachu — Sat, 17 Aug 2019 03:35:10 GMT

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-Table/m-p/767405#M25677

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

Re: Lookup different value depending on date column

Anonymous — Mon, 02 Sep 2019 12:31:34 GMT

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-Table/m-p/767405#M25677
have a read, and if something is not clear please post again