Skip to main content
cancel
Showing results for 
Search instead 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

Reply
RichyL1980
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
v-cherch-msft
Microsoft Employee
Microsoft Employee

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]
    )
)

1.png

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.

View solution in original post

5 REPLIES 5
RemonKissen
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

v-cherch-msft
Microsoft Employee
Microsoft Employee

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]
    )
)

1.png

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.

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

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

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.

This is the Data SheetThis is the Data Sheetsnip 2.jpg

 

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]
    )
)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.