Column wise rolling sum of n period in pivot table

[vikas12345]

In pivot table, I have one dimension in row and in column one dimension is monthname. I want to calculate rolling sum of sale column wise and I have two years of data. so there will be 24 columns. I am using expression for rolling sum as below,

Rangesum(before(Sum(Sales),0,ColumnNo(total)))

But my task is show current financial year data(From April to current month) in pivot table. How can I achieve this?

@sunny_talwar @marcus_sommer @kaushiknsolanki @rwunderlich @Digvijay_Singh  @Anil_Babu_Samineni @Michael_Tarallo 

[marcus_sommer]

Maybe something like this:

Rangesum(before(Sum({< FinancialYear = {2021}>} Sales),0,ColumnNo(total)))

Of course you need to have this field within the data-model. You may reach this also with other conditions on month or similar but linking the normal calendar with a financial calendar will simplify many tasks.

- Marcus

[vikas12345]

But by using this, rolling sum will be start from April 2021 and previous values will not include in rolling sum.

[marcus_sommer]

Yes, interrecord-functions will only use those cells which are available. Thinkable is to extend the logic with an aggr() to include within the inner aggregation a wider period and restricting it within outer aggregation to a smaller area. But it may not very trivial to implement such a logic.

Another approach could be to display just a few more periods as for the real purpose needed and you may also add another dimension to the object so that you have there the financial year, too.

- Marcus

[vikas12345]

@Gysbert_Wassenaar @jagan @DavidFoster1