Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need to the latest / most recent non blank selling price and cost price as per customer and item name.
I had used this calculation for a calculated column as suggested
RECENT DATE = IF(CALCULATE(MAX(Table1[Date_]),ALLEXCEPT(Table1,Table1[Item Name],Table1[Customer Name]))=Table1[Date_],"Y")
This formula works and I was able to filter by dept.
But i require the last non-blank value of the selling price and another issue with this formula is that if there are 3 transactions done for that day it sums the selling price of all 3 transactions rather than giving me a single amount.
For eg. the most recent transaction of bbq sauce (in the given data) is on 10/4/23 but selling price is blank so i require the last time selling price was not blank for bbq sauce by customer and item.
I have uploaded the data and the desired result.
Solved! Go to Solution.
@wmf_07
Create the following two measures instead of columns:
SalesRate = LASTNONBLANKVALUE( Table06[Date_] , CALCULATE( MAX( Table06[Sales Rate] ) ) )
UnitCost = LASTNONBLANKVALUE( Table06[Date_] , CALCULATE( MAX( Table06[Unit Cost] ) ) )
Result:
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@wmf_07
Create the following two measures instead of columns:
SalesRate = LASTNONBLANKVALUE( Table06[Date_] , CALCULATE( MAX( Table06[Sales Rate] ) ) )
UnitCost = LASTNONBLANKVALUE( Table06[Date_] , CALCULATE( MAX( Table06[Unit Cost] ) ) )
Result:
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thank you 👍
@wmf_07
You are welcome!
Kinldy accept the soulution for the same question here as well: Obtain the last non blank selling price and cost p... - Microsoft Fabric Community
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
57 | |
21 | |
21 | |
19 | |
16 |
User | Count |
---|---|
86 | |
84 | |
52 | |
37 | |
23 |