Rolling 12 month rangesum problem

[Akakaboto]

Hello Everyone.

I have a data set streching from 2009-2019.

I am trying to create a graph with our inventory turnover currently defined as:

-sum(aggr(rangesum(above(sum([3345232000 Net Sales, total]),0,12)),[Business region], [Sub region], YearMonth))/1000
/
(
sum(aggr(rangesum(above(sum([1510000000 Inventories]),0,3)),[Business region], [Sub region], YearMonth))/3/1000
)

So basically it takes 12 month Net sales / 3 month average inventory value.

Now to my problem:

The first 12 month will always show incorrect values because of my rangesum formula. If I were to selct 2018-01 to 2018-12 in my YearMonth filter only 2018-P12 would be correct so for that reason I would have to select 2017-01 to 2018-12 to have 2018 values correct.

how can I re-write this to look at max YearMonth selected and then 12  month back with correct values for all month.

Monthnum is my dimension for month serial number...So I want to look at max YearMonth selected in my filter and then calculate 12 month back based on Monthnum.

[sunny_talwar · Accepted solution]

May be this

-Sum(Aggr(RangeSum(Above(Sum({<YearMonth>} [3345232000 Net Sales, total]), 0, 12)), [Business region], [Sub region], YearMonth))/1000
/
(
Sum(Aggr(RangeSum(Above(Sum({<YearMonth>} [1510000000 Inventories]), 0, 3)), [Business region], [Sub region], YearMonth))/3/1000
)

[Akakaboto]

---

@sunny_talwar wrote:

May be this

-Sum(Aggr(RangeSum(Above(Sum({<YearMonth>} [3345232000 Net Sales, total]), 0, 12)), [Business region], [Sub region], YearMonth))/1000
/
(
Sum(Aggr(RangeSum(Above(Sum({<YearMonth>} [1510000000 Inventories]), 0, 3)), [Business region], [Sub region], YearMonth))/3/1000
)

---

Thank you Sir!