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.
Hi Everyone,
I have two tables, sales table and price table as below.
Sales Table
Sales ID | Item ID | Invoice Date |
SO0001 | IT0002 | 30/04/2018 |
SO0002 | IT0002 | 30/05/2019 |
Price Table
Item ID | From Date | End Date | Price |
IT0002 | 01/01/1900 | 15/04/2018 | $100.00 |
IT0002 | 16/04/2018 | 15/05/2018 | $101.00 |
IT0002 | 16/05/2018 | 01/01/1900 | $102.00 |
The relationship on Item ID will be many to many between these two tables. Noted that the dafult setting for null date is 01/01/1900.
Is there any way that I can do in Power BI to get the correct price ($102.00) for Sales ID SO0002? Thanks
Solved! Go to Solution.
@Penn try following calculated columns, In expression, change table and column name as per your data model.
Price = VAR itemid = Table4[Item ID] VAR invdate = Table4[Invoice Date] RETURN CALCULATE( MAX( Table3[Price] ), Table3[Item ID] = itemid, invdate >= Table3[From Date] , invdate <= IF( YEAR( Table3[End Date] ) = 1900, invdate, Table3[End Date]) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Penn try following calculated columns, In expression, change table and column name as per your data model.
Price = VAR itemid = Table4[Item ID] VAR invdate = Table4[Invoice Date] RETURN CALCULATE( MAX( Table3[Price] ), Table3[Item ID] = itemid, invdate >= Table3[From Date] , invdate <= IF( YEAR( Table3[End Date] ) = 1900, invdate, Table3[End Date]) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @parry2k
You are absolutely a legend! Never thought that I can do this in just one step. Learned something new today. Thanks
@Penn glad to help.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
114 | |
100 | |
78 | |
75 | |
50 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |