Forum Discussion
Solved
How do I get a value from another table based on a date range
Hi, I have a table with multiple Date Ranges (table called "DateRanges"): Column1 Cycle CycleStartDate CycleEndDate 2 1 3/31/2022 4/13/2022 2 ...
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
Hello EaglesTony ,
Here is the DAX for you assuming T_1 and T_2 are first and Second tables respectively with data given :
NewTable =
ADDCOLUMNS(
T_2,
"Value",
CALCULATE(
MAX(T_1[Cycle]), -- Use MAX to get the value from T_1 (could be SUM, MIN, etc. depending on your needs)
FILTER(
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.
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.
