Forum Discussion
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 2 4/15/2022 4/27/2022
2 3 4/28/2022 5/11/2022
2 4 5/12/2022 5/25/2022
I have another table that has a date and I need to get the Cycle from the first table:
Key Date
1 4/20/2022
2 5/25/2022
I would want another field, so it looks like:
Key Date Cycle
1 4/20/2022 2
2 5/25/2022 4
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
22 Replies
Hello EaglesTony,
You can create a Dim_Date table and make relationship to the other tables that have include date. After complete relationship, for cycle start or cycle end date, you might be need a dax code to use USERRELATIONSHIP.
Kind Regards,
Gökberk Uzuntaş
📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!
🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok |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!
I like this approach and seems to work.
How now do I get this Cycle value to another table in DAX both tables have Key as a column.
If I now have the following in a table called Table2:
Key Date Cycle
1 4/20/2022 2
2 5/25/2022 4
I have table1 with(notice no Key 2):
Key Column1 Column2
1 John Doe
3 Jane Doe
I need table1 to be:
Key Column1 Column2 Cycle
1 John Doe 2
3 Jane Doe null
Hello,
To bring the Cycle value from Table2 into Table1 you can create a calculated column in Table1 that looks up the corresponding Cycle from Table2 using the common Key column.
Create a Relationship:
Ensure that there is a relationship between Table1 and Table2 using the Key column. If the relationship doesn’t already exist, go to the Model view and create a one-to-many relationship from Table1[Key] to Table2[Key].Create a Calculated Column in Table1:
Go to Modeling and select New column.
Write the following DAX formula to create a calculated column that brings in the Cycle value from Table2:Cycle = RELATED(Table2[Cycle])
The RELATED function is used to fetch a value from a related table. In this case, it retrieves the Cycle from Table2 where the Key matches.
Result:After applying the formula, Table1 will now have a new Cycle column that contains the matching Cycle from Table2 for each Key. If there is no matching Key, it will return null.
Let me know if you have any more questions!
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.
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 ?
