Forum Discussion
Need Help With LOOKUPVALUE or need ideas
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 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