Search value between two dates

[frankhofmans]

hi PBI experts,

i have 2 tables (with >100k rows)

table 1: orders per provider

Client ID	Order date	Order nr.
001	01-03-2022	111
001	02-04-2022	112
002	10-04-2022	113
003	13-04-2022	114
004	20-04-2022	115
004	03-05-2022	116
004	27-05-2022	117
005	02-06-2022	118

table 2: provider overview

Start	End	Client ID	Purchase price
01-01-2022	01-01-9999	001	20
01-01-2022	01-03-2022	002	25
01-03-2022	01-01-9999	002	27
01-01-2022	01-01-9999	003	25.5
01-01-2022	01-04-2022	004	25.7
01-04-2022	01-05-2022	004	26
01-05-2022	01-01-9999	004	26.3
01-01-2022	01-01-9999	005	25.8

if i want to have the purchase price for provider 004 on 20-04-2022, it has to be 26 (the price between 01-04-2022 and 01-05-2022). So i want to have the following results:

Client ID	Order date	Order nr.	Purchase price
001	01-03-2022	111	20
001	02-04-2022	112	20
002	10-04-2022	113	27
003	13-04-2022	114	25.5
004	20-04-2022	115	26
004	03-05-2022	116	26.3
004	27-05-2022	117	26.3
005	02-06-2022	118	25.8

Does anyone have a solution for this?

Thanks in advance,

Regards,

Frank

[NickolajJessen · Accepted solution]

Hey Frank,

try something like this:

I created a dimension table for the customer as is good practice and create a measure as so:

If this post solves your problem, accept it as a solution
Appreciate your kudos