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
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!
Is there a way to do this in Power Query instead ?
1.
Load both DateRanges and the second table (with Key and Date) into Power Query.
2.Select the Transactions table.
Go to the Add Column tab and select Custom Column.
Write a custom column to check if the date in the Transactions table falls between the CycleStartDate and CycleEndDate in the DateRanges table.You can write this in a Custom Column:
List.Select( DateRanges, each [CycleStartDate] <= [Date] and [CycleEndDate] >= [Date] )
This will return all matching rows from DateRanges. You’ll want to expand that column to include the Cycle value.After creating the custom column, expand the new column (click on the two arrows next to the column name) and select only the Cycle column from the DateRanges table.
Now your Transactions table will have the Cycle values based on the date range.3.
Now that your Transactions table has the Cycle, you can merge it with Table1 based on the Key column.Select Table1 in Power Query.
Click on Home > Merge Queries.
Choose Table1 as the first table and Transactions as the second table.
Select the Key column from both tables to perform the merge.
Use a Left Outer Join so that all rows from Table1 are retained.
After the merge, expand the Cycle column from the Transactions table.
4.After the merge, Table1 will now contain a Cycle column with the corresponding value from Transactions, based on the Key and Date.
If there’s no matching Key in Transactions, the Cycle will be null.For Step #2, how can I reference the fields in DateRange table, as the only fields available are in the Transactions table ?