Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shubh2488
Regular Visitor

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

 

shubh2488_0-1616695564945.png

 

Please help me with this.

 

Regards

Shubham

 

 

 

 

 

 

3 REPLIES 3
Anonymous
Not applicable

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?

 

rfigtree
Resolver III
Resolver III

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])
	))
ERD
Super User
Super User

Hello @shubh2488 ,

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!

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors