Cumulative sum based on other columns

[georgec96]

Hi everyone,

I've been struggling to write a calculated column to solve the following issue.

I've got 4 columns: SKU,PO,Qty ordered and Qty on backorder. I need to create a column called "can_clear_backorders", that will calculate the amount of backorders a PO can clear based on the qty ordered but this needs to be a cumulative sum and it also needs to reset when there is a new value in column SKU.

Any ideas how can I achive this? Sorry if my explanation is not clear enough, I've attached an image with the expected result as an example.

[v-zhangti · Accepted solution]

Hi, @georgec96 

You can try the following methods. You need to add an index column to Power Query first.

Column:

Max Oty on backorder = MAXX(FILTER('Table',[SKU]=EARLIER('Table'[SKU])),[Qty on backorder])

Cumulative = CALCULATE(SUM('Table'[Qty ordered]),FILTER('Table',[SKU]=EARLIER('Table'[SKU])&&[Index]<EARLIER('Table'[Index])))

Result = 
SWITCH(TRUE(),
 [Cumulative] = BLANK ()&& [Qty ordered] <= [Qty on backorder], [Qty ordered],
 [Cumulative] = BLANK ()&& [Qty ordered] > [Qty on backorder],[Qty on backorder],
 [Max Oty on backorder]-[Cumulative]<0,0,
 [Qty ordered]<[Cumulative],[Qty ordered],[Max Oty on backorder]-[Cumulative])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@georgec96 , You back order logic is not clear.

But windows function with partitioned by can help

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

https://www.youtube.com/watch?v=cN8AO3_vmlY&t=31980s

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.