Cumulative sum column for the table

[Anonymous]

Hello 

I have a table containing, Date, category1 (Fund), category 2(Board), category 3(IEO), net balance 
I need a cumulative sum of the net balance for all 3 categories with a month filter in it :

I have used DAX for the cumulative sum for the month which is working using :
Cumm actual =

Var Maxd = MAX(new_shptransactionscurrentyears[new_postingdate])

Return

   CALCULATE(SUM(new_shptransactionscurrentyears[new_netbalance]),ALLSELECTED(new_shptransactionscurrentyears) ,(new_shptransactionscurrentyears[new_postingdate] <= Maxd))

& Tried 

CALCULATE(SUM(new_shptransactionscurrentyears[new_netbalance]),filter(allselected(new_shptransactionscurrentyears[new_postingdate]),new_shptransactionscurrentyears[new_postingdate] <=max(new_shptransactionscurrentyears[new_postingdate])))

This worked fine for the month table however doesn't work on the table with categories in it or with filter on 

Thank you & I really appreciate your help 
#Need Help

[Anonymous]

Hi @Anonymous ,

Cummulative 2 =

VAR PriorMonth = CALCULATE(SUM(Sheet2[Final_1]),DATEADD(DateTable[Date],-1,MONTH))

VAR CumlTotal = SUM(Sheet2[Final_1])+ PriorMonth

return

CumlTotal

I tried with above DAX not sure if it will work but if it does pls do let me know.

Note:
I have created a separate date table and instead of Sheet2[Final_1] use the column you need. It is just for reference.

[Anonymous]

Hello @Anonymous 

Thank you for the DAX, It works absolutely fine for the cumulative sum per month 
I require cumulative sum per month & per category (Cumulative sum should happen by keeping category as well)

Hope I'm clear with explanation 

Thank you 

[Anonymous · Accepted solution]

Hi @Anonymous ,
Pls refer below image. I thought you need sum of current month+ previuos month for each of three category. I have highlighted one for your reference. 

If this is still incorrect, I guess I am still not clear on your requirement.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I cannot help you without meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous]

Hello @lbendlin 

Please find the sample dataset
https://docs.google.com/spreadsheets/d/1YSsTZym_-QGu_yC_qclEeQPcBsYmEtSh/edit?usp=sharing&ouid=11617...

Looking forward to discussing with you further 

[lbendlin]

I don't understand the logic behind the expected value. Please explain the first couple of rows.

[Anonymous]

Hello @lbendlin 

I apologize for not being descriptive with the question, Allow me to explain the problem.
I have a table called sheet 1 containing Fund, Board & IEO, Balance & pdate
I want a calculated column to take out the cumulative sum via pDate group by 3 columns(Fund, Board, Ieo)



For example 

Fund	IEO	Board	Post Date	balance	Cumulative bal
Restricted	income	rent/charges	1/03/2022	20	20
Restricted	income	Support charges	5/03/2022	30	30
Restricted	expenditure	Staff cost	1/03/2022	5	5
Restricted	income	rent/charges	20/03/2022	10	30
Unrestricted	income	rent/charges	5/03/2022	-20	20
Unrestricted	income	rent/charges	1/04/2022	50	30

The column would be much better as apply filter still works fine with different month filters rather than measure 

The solution is much appreciated & most awaited (Brain storming since days)
Thank you  🙂 

[lbendlin]

Unless you are able to show me step by step how you arrived at the expected outcome values I won't be able to assist. I haven't understood the logic yet.

[Anonymous]

Hello @lbendlin

Any luck ?
 

[Anonymous]

Hello @lbendlin 

I need a Cumulative sum of balance considering 3 categories as a group

Fund

IEO

Board

Bal

The balance should be grouped by the three above categories & make a cumulative balance by the date column

For instance : 

Fund	IEO	Board	Balance
Restricted	income	rent	20

The cumulative sum should calculate the sum of the balance when the same combination occurs in the future/down the line(calculated via dates)

Fund	IEO	Board	Bal	Cumm Balance	Date
Restricted	income	rent	20	20	1/08/2022
Restricted	income	rent	10	30	10/08/2022

For different combinations, it should store the same value until the next same combination is occurred which later be added to the previously occurred sum

Sorry if I couldn't be able to explain it better, I Can even contact you through other sources if this hasn't helped you understand the issue

better demonstrated on the Matrix table (looking forward to the below dashboard)
Clearly, the cumulative value is not working when the filter is selected 

 I need the sum of values for the previous month + the current month  in the cumulative section 

For instance: when April is selected: I need a sum of the previous month march +current month April
select march = sum(feb+march)

Imaginative purpose dashboard