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

[shamsu_mk]

It is working. I expect some issue may happen if i link to my original report. This formula i am using for some bank covenants report. I will continue with this way and if I face any issue let us see later.

Once again thank you.

Also thanks to all members who are comment on my query.

[andrei_delta]

Hello,

Look at this post: Set Analysis for Rolling Periods

[shamsu_mk]

Hi

The following is the formula from my existing report.

sum({< [Exec P&L Heading] = {'EBITDA'}>} Amount)/1000000*-1

I want to replace EBITDA from the above formula with the below mentioned formula we have made. Please support.

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

[sunny_talwar]

Not sure I understand... do you want to elaborate?

[shamsu_mk]

Hi

Sorry, I think I confused you.

The below is the expression we recently developed.

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

We already had the data in the table for [EBITDA M].

in my new report, I need to calculate [EBITDA M]) and the formula is as follows.

sum({< [Exec P&L Heading] = {'EBITDA'}>} Amount)/1000000*-1

That means I want to replace with new formula as mentioned below.

RangeSum(Above(TOTAL Sum({<Year, Month>}[EBITDA M]) sum({< [Exec P&L Heading] = {'EBITDA'}>} Amount)/1000000*-1 , 0, 12)) * Avg(1)

Strikethrough has to be removed and replaced with the underline formula.

[sunny_talwar · Accepted solution]

May be this

RangeSum(Above(TOTAL Sum({< [Exec P&L Heading] = {'EBITDA'}, Year, Month>} Amount)/1000000*-1 , 0, 12)) * Avg(1)

[shamsu_mk]

Thank you. It is working now. Still, i have a small issue and I am checking that. Suppress zero is not working.

[shamsu_mk]

Hello

I still could not solve the issue to suppress zero. I selected only 2017 Jan to Sep. But it displaying all other years with zero value. I checked the "Suppress Zero Value" from Presentation tab option but still same. Please help me

[sunny_talwar]

May have you use an if statement for your indicator column in the end

[shamsu_mk]

Can you please give an example of if statement adding with the below formula ?

RangeSum(Above(TOTAL Sum({< [Exec P&L Heading] = {'EBITDA'}, Year, Month>} Amount)/1000000*-1 , 0, 12)) * Avg(1)