Forum Discussion
EaglesTony
Post Prodigy
Post ProdigyHow 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,
Use the following DAX formula to create the calculated column that returns the correct cycle from the DateRanges table:
Cycle =
CALCULATE(
MAX(DateRanges[Cycle]),
FILTER(
DateRanges,
Transactions[Date] >= DateRanges[CycleStartDate] &&
Transactions[Date] <= DateRanges[CycleEndDate]
)
)
This method should work for retrieving the correct cycle based on the date ranges in Power BI.
If this works for you please mark my answer as your solution.
Thank you!
EaglesTonyPost Prodigy
Post ProdigyIs there a way to do this in Power Query instead ?