Forum Discussion
How do I get a value from another table based on a date range
Hello EaglesTony ,
Simple use Lookup between FinalTable and ReportTable to get the value :
Cycle =LOOKUPVALUE(FinalTable [Cycle], -- Column to returnFinalTable [Key], ReportTable [Key] -- Matching Key from both tables)I have checked this with sample data :I hope this is what you are looking for .
Did I solve your Query ? Please mark this as solution. Appreciate Kudos always
Cheers
What is "New Table" ?...A column or a measure on a certain table ?
A new table .
Do you need a measure ?
Hello EaglesTony ,
If you want column added in second table, here is the code :
Cycle1 =CALCULATE(MAX(T_1[Cycle]), -- You can use SUM, MIN, etc. as neededFILTER(T_1,T_1[StartDate] <= T_2[Date] && T_1[EndDate] >= T_2[Date]))Please mark this as solution if this has solved your problem.
Hello EaglesTony
If you want a measure , use below dax:
Cycle =CALCULATE(MAX(T_1[Cycle]),FILTER(T_1,T_1[StartDate] <= MAX(T_2[Date]) &&T_1[EndDate] >= MAX(T_2[Date])))Please mark this as solution if this has solved your problem.I'm able to get the correct cycle on this table.(call it FinalTable)
My issue now is there is a 3rd table (ReportTable), which I need to get this field into, but the issue is that the 3rd table has Keys that some don't match the FinalTable.
Thanks for confirming first one is completed. Can you please add sample data for 3rd table ?