Forum Discussion
Helper IICumulative sum based on other columns
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.
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.
2 Replies
- amitchandak
Super User
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. 
v-zhangtiCommunity Support
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.
