Reverse Cumulate Alternative?

Hi - hoping to see if anyone has a solution to this:

In the following example, I'm trying to calculate (going backwards) when the sum (less current period) would be greater than the test doh line item.

In Jan 20, it would look back 3 periods to be greater than or equal to 92. (Oct/Nov/Dec)

In Mar 20, it would look back 4 periods to be greater than or equal to 92. (Nov, Dec, Jan, Feb)

I have tried various forms of CUMULATE, OFFSET, PREVIOUS... but haven't been able to create this logic.

Has anyone had success performing a reverse recursion?

Thanks in advance!

Jack

Accepted answers

All comments

nathan_rudman

one solution is: test all possibilities with a movingsum and a numbered list:

extracting the result with first non blank:

here's the list of possibilities:

there might be an easier way if we are really talking about calendar days and simple numbers.

but this solution will work for anything.

adube

Looks like a workable solution. thanks

AWhitworth

You could use that start date and period() function to find which month satisfies the 92 days prior. using a month period line item your formula would be Period(start() - test doh) from here you should easily be able to calculate how many months to look back.

Quick Links

Can you help?

Unanswered questions

Rolling forecast
Hi , I have a requirement for my stakeholder to have a medium term forecast (by month for the next 6 months) and a more detailed rolling 15-week sales forecast by day which will then feed into the wider Supply Chain planning processes over time. This needs to be updated by the Sales representants for consolidation by the…
AnaplanXL Difficulties
Has anyone found success using Anaplan XL with Polaris models in a shared drive with or without concurrent users? My team has experienced a variety of errors, login failures, etc. sometimes it works, sometimes it doesn't. We've been recommended to save and edit files locally, which helps but certainly with its limitations…
Using Anaplan for Managing Custom Product Configurations (Example: Designer Dolls)
We’ve recently started using Anaplan to model and manage our supply chain and production planning for a niche ecommerce business that sells custom-designed dolls — each made-to-order with specific features, materials, and delivery lead times. A few interesting challenges we faced: SKU Explosion: Each doll can be customized…