Forum Discussion
Solved
How to define if date is in a range
Hi,
I have the following problem. I need to relate the table of prices:
| StartingPrice | EndPrice | Product | Unit Price |
| 2021-03-01 | 2021-05-22 | Apple Tree | 1.24 |
| 2021-05-23 | 2021-07-31 | Apple Tree | 1.54 |
| 2021-08-01 | 2022-04-01 | Apple Tree | 1.80 |
| 2021-03-01 | 2021-05-22 | Bonsai | 2 |
| 2021-05-23 | 2021-07-31 | Bonsai | 1.86 |
| 2021-08-01 | 2022-04-01 | Bonsai | 1.79 |
With the sales tables, that contains an structure like this:
| Product | Quantity | Sales Date |
| Apple Tree | 123 | 2021-03-22 |
| Bonsai | 234 | 2021-05-09 |
| Apple Tree | 100 | 2022-01-22 |
How can I relate the two tables to obtain the Unit Price of each order. Because on Excel for me it's clear that with EndPrice column and XLOOKUP could be work, but on Power Query no.
Use below formula in a custom column in second table. First table is PriceTbl.
Table.SelectRows(PriceTbl, (x)=> x[StartingPrice]<=[Sales Date] and x[EndPrice]>=[Sales Date] and x[Product]=[Product]){0}[Unit Price]
1 Reply
Use below formula in a custom column in second table. First table is PriceTbl.
Table.SelectRows(PriceTbl, (x)=> x[StartingPrice]<=[Sales Date] and x[EndPrice]>=[Sales Date] and x[Product]=[Product]){0}[Unit Price]