Rolling 12-Months sum

[shamsu_mk]

Dear Team

I have EBITDA data on monthly basis. I would like to have an expression field for rolling-12 months sum in the field R-Sum2.

Please support. Attached the data file

Shamsu

[Anil_Babu_Samineni]

How about this?

Sum({<Month = {">=$(=AddMonths(Max(Month),-12)) <=$(=Max(Month))"}>} EBITDA)

[effinty2112]

Hi Shamsu,

For a rolling sum make a straight table with the expression you want to calculate the rolling sum on. Have the Year and Month dimensions in the table and/or the period dimension and make sure the table is sorted correctly. Go into the Expressions tab and for your expression choose Accumulate 12 steps back.

Good luck

Andrew

[sunny_talwar]

May be this?

RangeSum(Above(TOTAL Sum([EBITDA M]) , 0, 12))

[shamsu_mk]

Hi Anil

EBITDA M is my monthly EBITDA. If I use your formula I get the same numbers as EBITDA M. I have calculated the Rolling EBITDA in excel which showing in the field EBITDA R. The result should match with the EBITDA R

[shamsu_mk]

Dear Andrew

I did the arrangement in a way as you said. Please look in to my qvw file. Bt i did not understand that how to choose Accumulate 12 steps back.

[shamsu_mk]

Dear Sunny

Your reply is some what matching with my requirement, but a small issue. I have already calculated the Rolling EBITDA in excel and uploaded for checking purpose. You can see the field EBITDA R is the rolling 12 months EBITDA. That means "R.Sum2" should match with "EBITDA R"

I have a problem to use the function "Rage Sum " because my report will show only one month data at a time, it will not be a monthly table. Since it is showing only one month data it will not be possible to use Rage Sum

Also, i noticed that in some month the rolling EBITDA is not correct as per the formula is given by you.

If I select June 17, the rolling EBITDA should be the sum of EBITDA M from Jul16 to June 17. This is what i need.

[sunny_talwar]

Check this

RangeSum(Above(TOTAL Sum({<Year, Month>}[EBITDA M]) , 0, 12)) * Avg(1)

[shamsu_mk]

Dear Sunny

Thank you very much. It is working perfectly. But I have a question. Did you suppress zero?

When I use this formula it is displaying all the years and months as zero and the selected month shows the value. Is there any other way without suppressing zero to display only the selected period?

[sunny_talwar]

Yes, I did do suppress zero... is that not working?