cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nread Frequent Visitor
Frequent Visitor

Lookup different value depending on date column

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:

 

Annotation 2019-08-16 163238.png

 

Any help much appreciated!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Stachu Super Contributor
Super Contributor

Re: Lookup different value depending on date column

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Stachu Super Contributor
Super Contributor

Re: Lookup different value depending on date column

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

nread Frequent Visitor
Frequent Visitor

Re: Lookup different value depending on date column

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


 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)