Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a data model with 2 fact tables: SalesData, ItemReceived, date and product table.
I want to create a simple table visual with Date, Average Selling Price (From Sales Data), Cost (From ItemReceived). There is a slicer to select product name.
Issue is with the Cost. Let's say, I have item received on 4/2/2024 and cost is $116. For any dates on or later than 4/2/2024, my cost is $116. Earlier item received was on 1/20/2024 and cost is $90. So, any dates between 1/20/2024 and 4/1/2024 has product cost of $90. This way I will calculate my profit like (ASP - COST)
I used below measure:
LastCostMod =
CALCULATE(
LASTNONBLANKVALUE(itemReceived[Date],MAX(itemReceived[Cost])),
ALL(dimDate),
itemReceived[Date] <= SELECTEDVALUE(dimDate[Date])
)
It gives me good result on rows where I have dates. But it does not give me result on total and on Year-Month Tables.
Another solution could be to add cost on every row of my salesData. But it is not a good idea as I have sales data rows in millions.
How can I make a good measure so that it can also work on Year-Month table and totals?
My data model looks like this:
Please help. Thanks in advance
Solved! Go to Solution.
@tc_WII I think it should be:
LastCostMod =
CALCULATE(
LASTNONBLANKVALUE(dimDate[Date],MAX(itemReceived[Cost])),
FILTER(
ALL(dimDate),
dimDate[Date] <= MAX(dimDate[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.
@tc_WII I think it should be:
LastCostMod =
CALCULATE(
LASTNONBLANKVALUE(dimDate[Date],MAX(itemReceived[Cost])),
FILTER(
ALL(dimDate),
dimDate[Date] <= MAX(dimDate[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.
This worked for now. Thank you 🙂
User | Count |
---|---|
106 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |