Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |