Dynamic Rolling Sum - 4 Quarters trailing

[Olip]

Hello Folks,

I have a requirement to compute rolling sum for the trailing quarters with a dynamic range from Current Quarter till the Last Quarter of the minimum Fiscal Year in the data set. For  an example, if the data set starts from '2013 Q 1' till '2018 Q 1' then the trailing quarters should be displayed from 2017 Q 4 till 2013 Q 4. Using a formula like '' works but does not take care the start and end of the  quarter:

'rangesum( above ( sum([Reported PEB]),0,4) )'

Any thoughts to achieve this. See below highlighted in green is desired and yellow highlighted is what I get now.

[sunny_talwar]

So you would want to only see the green cells because those are completed quarters and have last 4 quarter available to calculate 12 Month Trailing?

Also, do you have MonthField in your dashboard or do you just use Today() to determine if the quarter is complete or not?

[Olip]

Yes for the first part of your reply.

For the second part not sure of the entire requirement but required calendar columns(month,etc) can definitely be added.

[sunny_talwar · Accepted solution]

From the data you provided you can do this

=If(Only({<FQ>} FQNum) >= Min({1}TOTAL  FQNum, 4) and Only({<FQ>} FQNum) <= Num#(Date(MonthStart(Today()), 'YYYYMM'), '##'), RangeSum(Above(Sum({<FQ>}[Reported PEB]), 0, 4)))

But there are some better ways to do this if you have other fields such as MonthYear...etc....

[Olip]

Thanks stalwar1‌ Can you share a few ideas with calendar functions to achieve this?

[sunny_talwar]

Is this the only date and time related field do you have? No dates or MonthYear in your dashboard?

[Olip]

Also can you elaborate on the column FQnum?

[sunny_talwar]

Just a numeric representation of FQ created in script

SubField(FQ, ' Q ', 1)*100 + SubField(FQ, ' Q ', 2)*3 as FQNum,

[Olip]

Not at the moment, however, a date dimension can be introduced.

Hence, I am more eager to have a better solution that incorporates calendar function to achieve this.

[juraj_misina]

Hello Alip,

I recommend this approach quite often and I think it can be used in your case as well: The As-Of Table

Juraj