Refering to other months in the formula using AFTER and ADDMONTHS fails

[Emil_bros]

Hi Colleagues,

I have a simple formula to implement in pivot table:

current month stock / next month sales  * 30

and this is to be calculated for each selected month separately, also quartely cumulatuion must be active.

I have two approaches but none of it works:

1) using AFTER function failes because of month/quarter/halfyear cumulation.

where SALES NEXT MONTH  =  after(sum(Sales),1)

              DAYS = (sum(Stock) / after(sum(Sales),1) *30

function AFTER won't properly assign next month data for every 3rd month of quarter and I don't know how to solve this.

2) using AddMonths fails to return next month data:

 where SALES NEXT MONTH =  sum({<Month = {'$(vNM)'} Sales)
              vNM = (DATE(ADDMONTHS(Month,+1),'MM-YYYY'))

with this approach I can't even get next month data.

Please advise how to solve this problem,

Best,

Emil

[sunny_talwar · Accepted solution]

Try adding TOTAL qualifier after the 'AFTER' function

After(TOTAL Sum(Sales), 1)

Although I suggest looking into  The As Of Table for this kind of requirement

[Emil_bros]

thanks again Sunny! Working perfectly