topic LOOKUPVALUE with ID and Date Filters in DAX Commands and Tips

LOOKUPVALUE with ID and Date Filters

NotVeryGoodatBI — Mon, 29 Jan 2024 14:43:35 GMT

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 MarginTable2 based on the ID and 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

Re: LOOKUPVALUE with ID and Date Filters

talespin — Mon, 29 Jan 2024 19:06:04 GMT

hi NotVeryGoodatBI ,

Please check LOOKUPVALUE function.

Re: LOOKUPVALUE with ID and Date Filters

NotVeryGoodatBI — Mon, 29 Jan 2024 19:32:36 GMT

Re: LOOKUPVALUE with ID and Date Filters

NotVeryGoodatBI — Mon, 29 Jan 2024 19:32:03 GMT

Hi talespin,

I know I need LOOKUPVALUE, I just need help writing the correct syntax to take into account multiple filters. I need to pull the correct margin for each invoice from the margin table where the IDs match and the invoice timeframe is within the margin timeframe.

Re: LOOKUPVALUE with ID and Date Filters

Anonymous — Tue, 30 Jan 2024 07:41:06 GMT

Hi NotVeryGoodatBI ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a calculated column.

created_field_margin = CALCULATE ( SUM ( 'Margin_Table'[Margin] ), FILTER ( 'Margin_Table', [ID] = EARLIER ( 'Invoice_Table'[ID] ) && [start_date] <= EARLIER ( 'Invoice_Table'[start_date] ) && [end_date] >= EARLIER ( 'Invoice_Table'[end_date] ) ) )

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.