Look Up String Value based on Transaction Date between Dates of Table

[Anonymous]

How would you write a DAX formula for a new measure that would lookup a string value from another table that contains employee position information and a start/stop date of that position.

Bob - Date: 2/15/2020  (that date will fluctuate because the transactional data table is formatted on a daily basis, and would be between the start/end date in the employee position table)

What is his position:  Should be Retail Associate

Employee Position Information

Employee	Position	Start Date	End Date
Bob	Training Associate	11/1/2019	1/7/2020
Bob	Retail Associate	1/8/2020	6/15/2020
Bob	Retail Manager	6/16/2020	12/31/2050

Expected outcome

Employee	Transaction Date	Sales	Expected Outcome
Bob	1/4/2020	3	Training Associate
Bob	2/15/2020	4	Retail Associate
Bob	3/16/2020	2	Retail Associate

[DataInsights · Accepted solution]

@Anonymous, try this solution:

1. Create a star schema data model:

2. Create measure:

Position = 
VAR vEmp = 
    MAX ( Employee[Emp ID] )
VAR vDate = 
    MAX ( EmployeeTransaction[Date] )
VAR vEmpTable =
    FILTER (
        ALL ( EmployeePosition ),
        EmployeePosition[Emp ID] = vEmp
            && vDate >= EmployeePosition[Start Date]
            && vDate <= EmployeePosition[End Date]
    )
VAR vPosition =
    MAXX ( vEmpTable, EmployeePosition[Position] )
RETURN
    vPosition

3. Result: