Forum Discussion
Cumulative measure based on multiplied values, changing when multiplier updates
Hello - I have a need to calculate the cumulative production $ value by item, which is the production output by month multiplied by the average item sales price for the month. The issue is creating a cumulative sum of this production value - even for items that haven't been produced recently, if the sales amount updates for the item, the typical way of creating a cumulative sum results in a value that will change when the underlying sales price multliplier changes.
Ideally, for the example below, the first item would have a production value of $5,000 for the single month in which it was produced and the cumulative value would use the production quantity * the sales price for the month in which it was produced, and cumulatively sum that through the present month without changing when the sales price changes. How should I adjust my cumultative measure to accomplish this?
(PBIX file with sample data showcasing the issue: https://we.tl/t-yNuzc7PEIE)
Thanks 🙂
cmfb , Make sure the month year in the table is coming from the calendar table and the join is single directional.
Try like
CALCULATE(
Sumx(SUMMARIZE('Item', 'Item'[Item No],
"Qty", [Production Quantity],
"Avg Price", [Average Item Price]), [Qty]*[Avg Price]),
FILTER(ALL('Calendar'),
'Calendar'[Date] <= max('Calendar'[Date]) )
)Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
5 Replies
- amitchandak
Super User
cmfb , Make sure the month year in the table is coming from the calendar table and the join is single directional.
Try like
CALCULATE(
Sumx(SUMMARIZE('Item', 'Item'[Item No],
"Qty", [Production Quantity],
"Avg Price", [Average Item Price]), [Qty]*[Avg Price]),
FILTER(ALL('Calendar'),
'Calendar'[Date] <= max('Calendar'[Date]) )
)Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4Thank you! I've accepted this one as the solution as it works great, and still works with time intelligence (can filter for current year, etc.).
- Ashish_Mathur
This works great -
SUMX(SUMMARIZE(CALCULATETABLE(VALUES('Calendar'[YYMM NUM]),DATESBETWEEN('Calendar'[Date],minx(all('Calendar'),'Calendar'[Date]),max('Calendar'[Date]))),'Calendar'[YYMM NUM],"ABCD",[Production Value]),[ABCD])
Thanks so much Ashish_Mathur - can I ask how you got the file size to be so much smaller than the original?- Ashish_Mathur
You are welcome. I did not do anything to reduce the file size.
