Forum Discussion
Helper IGet Id from second table based on date in first table and date range in second table
I have a hire date in my first table (T1), and I need to create a calculated column in T1 that gets the ID value from a second table (T2), based on the date range in T2. To illustrate, here is some sample data from T1:
And here is the data from T2:
What I need to do is create a calculated column for T1 that finds the date range T2.ProgramStartDate to T2.ProgramEndDate that contains T1.HireDate, and then returns T2.ID to that calculated column (if you compare the hire dates in T1 to the date ranges for the programs in T2, you can see that the corresponding ProgramIDs have been added to T1):
Does anyone know how I can do this?
Thanks.
Got it. It actually turned out to be very simple:
ProgramID = CALCULATE(VALUES(T2[ID]), FILTER(T2,T2[ProgramStartDate]<=T1[HireDate]&&T2[ProgramEndDate]>=T1[HireDate]))
Don't know why it took me almost two hours, and multiple, far more convoluted formulae to figure it out, but this did the trick for me.
1 Reply
jetboy2kGot it. It actually turned out to be very simple:
ProgramID = CALCULATE(VALUES(T2[ID]), FILTER(T2,T2[ProgramStartDate]<=T1[HireDate]&&T2[ProgramEndDate]>=T1[HireDate]))
Don't know why it took me almost two hours, and multiple, far more convoluted formulae to figure it out, but this did the trick for me.