Lookup Price within Date Range by delivered Date

[RichyL1980]

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

[RemonKissen]

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

[v-cherch-msft · Accepted solution]

Hi @RichyL1980 

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

[RichyL1980]

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

[v-cherch-msft]

Hi @RichyL1980 

Could you show me your data or some screenshots?You can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.Do you create relationships?

Regards,

Cherie

[RichyL1980]

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]

    )

)