Cumulative measure based on multiplied values, changing when multiplier updates

[suspectdevice]

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 🙂

Production Value =

VAR table_ =

SUMMARIZE('Item', 'Item'[Item No],

    "Qty", [Production Quantity],

    "Avg Price", [Average Item Price])

RETURN

IF(HASONEFILTER('Item'[Item No]), [Production Quantity] * [Average Item Price], SUMX(table_, [Qty] * [Avg Price]))

Production Value Cumulative =

VAR maxdate = MAX('Calendar'[Date])

RETURN

CALCULATE([Production Value],

    FILTER(ALL('Calendar'),

    'Calendar'[Date] <= maxdate)

)

[amitchandak · Accepted 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...

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[suspectdevice]

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.

[amitchandak · Accepted 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...

[suspectdevice]

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.).