Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 🙂
Solved! Go to Solution.
@suspectdevice , 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-bd52912a5b...
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
This works great -
Thanks so much @Ashish_Mathur - can I ask how you got the file size to be so much smaller than the original?
You are welcome. I did not do anything to reduce the file size.
@suspectdevice , 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-bd52912a5b...
Thank 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.).
User | Count |
---|---|
89 | |
73 | |
69 | |
64 | |
56 |
User | Count |
---|---|
98 | |
92 | |
84 | |
74 | |
66 |