Forum Discussion
Search value between two dates
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
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
1 Reply
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