Need Help With LOOKUPVALUE or need ideas

[NotVeryGoodatBI]

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

[NotVeryGoodatBI]

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

[Anonymous]

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

[NotVeryGoodatBI]

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

PREVIEW

[Anonymous · Accepted solution]

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

[lbendlin]

Why would an invoice have a start and end date?  What should happen if the invoice time interval spans two or more margin intervals ?