Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Reverse Cummulative Sum

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

 

 

 

 

 

 

cummulative dax
DAX
Desktop
power bi

3 Replies

  • ERD's avatar
    ERD
    Community Champion
    5 years ago

    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!

  • rfigtree's avatar
    rfigtree
    Resolver III
    5 years ago

    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])
	))
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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?