Announcements
cancel
Showing results for
Did you mean:
Contributor III

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

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)

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

Best,

Emil

1 Solution

Accepted Solutions
MVP

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

2 Replies
MVP

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

Contributor III
Author

thanks again Sunny! Working perfectly

Community Browser