topic Re: Reverse Cummulative Sum in DAX Commands and Tips

Reverse Cummulative Sum

Anonymous — Thu, 25 Mar 2021 18:16:13 GMT

Hello All,

I need help with creating a column/measure in Power BI Desktop.

I want to create a reverse cumulative sum based on multiple columns below is the dataset(example):

The last column is the result I want (cumm_qty):

Example:

for the combination: "20-DL-1051001002-2021" (has three rows for one for each month )

now I want a "cumm_qty" in reverse order i.e for 3rd month the value is -30495, for 2nd month it's 30495, for third month its 19000.

So for 3rd month the value should be same (-30495), for 2nd month the value should be (-30495+30495 = 0), for 1st month it should be (-30495+30495+19000 = 19000).

So If I add one more month then the calculation should be:

4th month = 4th month qty

3rd month = 4th month qty + 3rd month qty

2nd month = 4th month qty + 3rd month qty + 2nd month qty

1st month = 4th month qty + 3rd month qty + 2nd month qty + 1st month qty

Please help me with this.

Regards

Shubham

Re: Reverse Cummulative Sum

ERD — Fri, 26 Mar 2021 13:19:03 GMT

Hello Anonymous ,

You can use this measure:

CummulativeQty = var currentId = SELECTEDVALUE('Table'[id]) var currentWH = SELECTEDVALUE('Table'[WH]) var currentCode = SELECTEDVALUE('Table'[Code]) var currentYear = SELECTEDVALUE('Table'[Year]) var currentMonth = SELECTEDVALUE('Table'[Month]) RETURN CALCULATE( SUM('Table'[Qty]), FILTER( ALL('Table'), 'Table'[id] = currentId && 'Table'[WH] = currentWH && 'Table'[Code] = currentCode && 'Table'[Year] = currentYear && currentMonth <= 'Table'[Month] ) )

Did I answer your question? Mark my post as a solution!

Re: Reverse Cummulative Sum

rfigtree — Sat, 27 Mar 2021 09:24:09 GMT

or calculated column with similar formula.

=CALCULATE(SUM(Table1[qty]), filter(Table1, Table1[id]=EARLIER(Table1[id]) && Table1[wh]=EARLIER(Table1[wh]) && Table1[code]=EARLIER(Table1[code]) && Table1[year]=EARLIER(Table1[year]) && Table1[month]>=EARLIER(Table1[month]) ))

Re: Reverse Cummulative Sum

Anonymous — Sun, 28 Mar 2021 18:56:51 GMT

Small question for VAR and SELECTEDVALUE
if selected ALL in a filter, i got Blank() as result (in var), how can i solve the problem in Calculate?