Cumulative sum for current year

[vkal12]

Hello Community,

I have a question regarding the calcuation of a cumulative sum for the current year. My table looks like this at the moment: 

Date                         Amout      Amount this year

2022-12-05           460             460

2022-12-06           390             850

2022-12-07           420             1270 

2022-12-08           120             1390

In my actual scenario I would have a table, starting from 2022-01-01. I would like the have a cumulative sum over the field "Amount" for the whole year. I have used the following formula but it does not seem to work: 

RangeSum(Above([Amount]), 0, RowNo()))

Does anyone know where I am making a mistake?
Thank you very much in advance!

Kind regards,

Vanessa

[hic]

Is [Amount] a field or a measure? If it is a field, then you need to change your expression to 

RangeSum(Above(Sum(Amount)), 0, RowNo()))

[vkal12]

Hi,

the Field [Amount] is actually a measure that is calculated like this: COUNT(DINSTICT MR.Nr) 

Is there a way to handle this? 

[hic · Accepted solution]

If [Amount] is a measure defined in the same chart using COUNT(DISTINCT [MR.Nr]) then your RangeSum expression is correct and should work:

 RangeSum(Above([Amount]), 0, RowNo()))

What dimensions do you have? And what does the RangeSum expression return?

[vkal12]

I acutally use [Amount] as a master element but in my formula I would use it like this: 

RangeSum(Above(COUNT(DISTINCT MR.Nr)), 0, RowNo())

As a dimension I only use the current date so that my table looks like this:

Amount = COUNT(DISTINCT MR.Nr)

Amount this year = RangeSum(Above(Count(DISTINCT MR.Nr)), 0, RowNo())

I actually have no idea why the values look so "weird"... maybe as an additional information: i have a bookmark on this table that does only show the current month. The field "Amount this year" should show the amount from the last months + the amount for each day in the current month. 

[vkal12]

Hi hic,

I've played around a little bit and I found the solution: I've put the brackets wrong! The correct formual for "Amount this year" should be: RangeSum(Above(Count(Distinct MR.Nr), 0 RowNo()))

Additionally, I've included the Aggr()-Function at the front and now it works perfectly: 

Amount this year: Aggr(RangeSum(Above(Count(Distinct MR.Nr), 0 RowNo())), Year, Date)

Thanks for your support!