Cumulative sum per month for balance sheet account

[PLE]

Dear all, I have a table with general ledger movements, and I want a bar chart showing the sum of these amounts per month/year.

It's cumulative data, so the bar chart should show e.g. jan/2021 - feb/2021 - mar/2021 whereby the amount in each month is cumulative for the month in question and all previous months.

Below sample table of my data, with the column Desired Outcome what needs be shown in the bar chart.

YearMonth	Amount	Desired Outcome
2024-Jul	-98.164,39	88.433,27	all data
2024-Jun	41.816,93	186.597,66	all data up to jun 2024
2024-May	13.591,33	144.780,73	all data up to may 2024
2024-Apr	-228.636,43	131.189,40	all data up to apr 2024
2024-Mar	191.707,76		and so forth….
2024-Feb	-50.594,50
2024-Jan	95.192,49
2023-Dec	-2.823,69
2023-Nov	-44.201,88
2023-Oct	70.545,65
2023-Sep	100.000,00

This is the formula I tried, with no succes.

Sum({< [YearMonth] = {"<=$(=Max(YearMonth))"} >}
ActAmount)

Can somebody help please.

Many thanks,
Paul

[Kushal_Chawda]

@PLE  with Month Year in dimension you cannot use set analysis. Try below

=sum(aggr(rangesum(above(Sum({<YearMonth>}ActAmount),0,rowno(total))),YearMonth))

[PLE]

Hi Kushal, thanks for the tip, but this isn't working either. Am still looking for a solution.

What alternative do I have when not using month year but using the calander date itself ?

Thanks,

Paul

[Kushal_Chawda]

@PLE  what is not working? Could you send screenshots of what did you try?

[PLE]

Hi Kushal, attached printscreen of the formula I used.

You see in the second column the total that is correct, but the individual months don't show cumulative amounts.

[Kushal_Chawda · Accepted solution]

@PLE  If your YearMonth is Numeric, you can try sorting by Month in Aggr. Remove set analysis of YearMonth

=sum(aggr(rangesum(above(Sum({<YearMonth>}ActAmount),0,rowno())),(YearMonth,(NUMERIC)))

[PLE]

this works Kushal, thanks for your help, much appreciated.