Forum Discussion
Need Help With LOOKUPVALUE or need ideas
Hello, I am a little new to PowerBI and I am trying find a function like Excel's VLOOKUP that will allow me to create a column in Invoice Table 1 that pulls correct margin value from the time frame of the invoice instance. I need a function not a join please!
Table 1: Invoice_Table
| invoice_number | ID | start_date | end_date | Created_Field_Margin(Margin_Table) |
| 5456 | 1 | 10/20/2023 | 11/20/2023 | |
| 5115 | 2 | 1/19/2024 | 2/19/2024 | |
| 15156 | 3 | 3/25/2024 | 4/26/2024 | |
| 5155 | 4 | 1/18/2024 | 2/22/2024 | |
| 46565 | 5 | 11/20/2023 | 12/10/2024 |
Table 2: Margin_Table
| ID | start_date | end_date | Margin |
| 1 | 10/20/2023 | 1/10/2024 | 0.32 |
| 2 | 10/21/2023 | 1/11/2024 | 0.55 |
| 3 | 10/22/2023 | 1/12/2024 | 0.11 |
| 4 | 10/23/2023 | 1/13/2024 | 0.55 |
| 5 | 10/30/2023 | 1/14/2024 | 0.57 |
| 1 | 1/11/2024 | 5/21/2024 | 0.35 |
| 2 | 1/12/2024 | 5/22/2024 | 0.62 |
| 3 | 1/13/2024 | 5/23/2024 | 0.16 |
| 4 | 1/14/2024 | 5/24/2024 | 0.54 |
| 5 | 1/15/2024 | 5/25/2024 | 0.58 |
Desired Output:
This created Margin field would pull the Margin value from the Margin_Table based on the 'ID' and where the invoice falls within start_date and end_date
| invoice_number | ID | start_date | end_date | created_field_margin(Margin_Table) |
| 5456 | 1 | 10/20/2023 | 11/20/2023 | 0.32 |
| 5115 | 2 | 1/19/2024 | 2/19/2024 | 0.62 |
| 15156 | 3 | 3/25/2024 | 4/26/2024 | 0.16 |
| 5155 | 4 | 1/18/2024 | 2/22/2024 | 0.54 |
| 46565 | 5 | 11/20/2023 | 12/10/2024 | 0.57 |
Hi NotVeryGoodatBI ,
Please try the following methods and check if they can solve your problem:
1.Create a new column. Enter the following DAX formula.
Margin_table(Margin) = CALCULATE(SUM('Margin_Table'[Margin]), FILTER('Margin_Table', 'Margin_Table'[ID]='Invoice_Table'[ID]&&'Margin_Table'[start_date]<='Invoice_Table'[start_date]&&'Margin_Table'[end_date]>='Invoice_Table'[start_date]))2.Drag the new column to the Invoice_Table.
3.The result is shown below.
Best Regards,
Wisdom Wu
5 Replies
- lbendlin
Super User
Why would an invoice have a start and end date? What should happen if the invoice time interval spans two or more margin intervals ?
Hi NotVeryGoodatBI ,
If I understand correctly, the issue is that you want to find a function to create a column. Please try the following methods and check if they can solve your problem:
1.Create a new column. Enter the following DAX formula.
Column = LOOKUPVALUE(Margin_Table[Margin], Margin_Table[ID], Invoice_Table[ID], Margin_Table[start_date], Invoice_Table[start_date])2.Drag the column into the table visual.
3.The result is shown below.
4.You can also use the following link to learn the function.
LOOKUPVALUE function (DAX) - DAX | Microsoft Learn
If the above ones can’t help you get it working, could you please provide the desired result screenshot to make a deep troubleshooting? It would be helpful to find out the solution.
Best Regards,
Wisdom Wu
Hello v-jiewu-msft,
Thank you for your comment. The desired output is below. I need a calculated column function that will match the ID from Invoice_Table1 to the ID in Margin_Table2, as well as filter MarginTable2 for the time frame listed in Invoice_Table1, grabbing the margin each row by matching the ID and timeframe.
invoice_number ID start_date end_date Margin_table(Margin) 5456 1 10/20/2023 11/20/2023 0.32 5115 2 1/19/2024 2/19/2024 0.62 15156 3 3/25/2024 4/26/2024 0.16 5155 4 1/18/2024 2/22/2024 0.54 46565 5 11/20/2023 12/10/2024 0.57 Hi NotVeryGoodatBI ,
Please try the following methods and check if they can solve your problem:
1.Create a new column. Enter the following DAX formula.
Margin_table(Margin) = CALCULATE(SUM('Margin_Table'[Margin]), FILTER('Margin_Table', 'Margin_Table'[ID]='Invoice_Table'[ID]&&'Margin_Table'[start_date]<='Invoice_Table'[start_date]&&'Margin_Table'[end_date]>='Invoice_Table'[start_date]))2.Drag the new column to the Invoice_Table.
3.The result is shown below.
Best Regards,
Wisdom Wu
Hello, if anyone could help me with the formula to create a column in Invoice Table 1 that pulls correct margin value from Margin Table 2 based on the ID and time frame of the invoice instance. I need a function not a join please!
Table 1: Invoice_Table
ID start_date end_date Margin 1 10/20/2023 1/10/2024 0.32 2 10/21/2023 1/11/2024 0.55 3 10/22/2023 1/12/2024 0.11 4 10/23/2023 1/13/2024 0.55 5 10/30/2023 1/14/2024 0.57 1 1/11/2024 5/21/2024 0.35 2 1/12/2024 5/22/2024 0.62 3 1/13/2024 5/23/2024 0.16 4 1/14/2024 5/24/2024 0.54 5 1/15/2024 5/25/2024 0.58 Table 2: Margin_Table
invoice_number ID start_date end_date Margin_table(Margin) 5456 1 10/20/2023 11/20/2023 5115 2 1/19/2024 2/19/2024 15156 3 3/25/2024 4/26/2024 5155 4 1/18/2024 2/22/2024 46565 5 11/20/2023 12/10/2024 Desired Output:
This created Margin field would pull the Margin value from the Margin_Table based on the 'ID' and where the invoice falls within start_date and end_date:
invoice_number ID start_date end_date Margin_table(Margin) 5456 1 10/20/2023 11/20/2023 0.32 5115 2 1/19/2024 2/19/2024 0.62 15156 3 3/25/2024 4/26/2024 0.16 5155 4 1/18/2024 2/22/2024 0.54 46565 5 11/20/2023 12/10/2024 0.57
