cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Lookup Price within Date Range by delivered Date

I have a weekly price update and now need to match what the Price of a product was by the delivery date.

Table1

[Item]      [From Date]              [To date]                [Price]

99401      29 February 2016     06 March 2016      0.45

99401      08 March 2016         20 March 2016      0.35

99406      29 February 2016     06 March 2016      1.65

Table2

[Delvery Date]          [Item]        [Vol]

04/03/2016               99401       656

04/03/2016               99401       501

10/03/2016               99401       100

02/03/2016               99401       50

I am quite new to BI and can only think of ways to do this within Excel but I am trying to keep this all in BI

1 ACCEPTED SOLUTION
Microsoft Employee

You may create a calculated column as below:

```Column =
CALCULATE (
MAX ( Table1[Price] ),
FILTER (
Table1,
Table1[Item] = Table2[Item]
&& Table1[From Date] <= Table2[Delvery Date]
&& Table1[To date] >= Table2[Delvery Date]
)
)
```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Regular Visitor

Hello,

I'am a Power BI starter and I've got the same question as RichyL1980, the only difference is that in my table the [to date] is missing. I only have the column [From Date]. Is it possible to create with a formula a [to date]. So I can use the solution as mentioned in this blog?

Thank you

Microsoft Employee

You may create a calculated column as below:

```Column =
CALCULATE (
MAX ( Table1[Price] ),
FILTER (
Table1,
Table1[Item] = Table2[Item]
&& Table1[From Date] <= Table2[Delvery Date]
&& Table1[To date] >= Table2[Delvery Date]
)
)
```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Thanks for the reply I have replicated this into the sheet but its not returning any results.

Microsoft Employee

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

This is the Data Sheet

Column = CALCULATE (
MAX ( Sheet1[Amount in transaction currency] ),
FILTER (
Sheet1,
Sheet1[Item] = Data[Item]
&& Sheet1[From Date] <= Data[Delivery Date]
&& Sheet1[To date] >= Data[Delivery Date]
)
)