The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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
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
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
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
270 | |
129 | |
123 | |
100 | |
92 |