Previous Function Cut-Off Date

Hello,

I have a formula written that takes "Company Churn" / "Previous Revenue"

Previous revenue = PREVIOUS(Revenue)

The formula works for the individual months, but when it does the summary the rates are incorrect because it always has one month more worth of data in the revenue line item.

Is there a way to say "If the period is after the switchover date then 0 else *Insert formula*"?

Thanks in advance

Accepted answers

Pooja

Hi,

If I understand your requirement correctly, I think below solution will work.

Create Line item "Switchover+1" and update formula as "IF ISACTUALVERSION() THEN BLANK ELSE ITEM(Time)" and update summary to FIrst non blank.

In CHurn Rate line item update formula as "IF Time Period[SELECT: TIME.'FY22'] + 1 <= ITEM(Time) THEN 0 ELSE Churn / PREVIOUS(Revenue)".

By this rate after switchover date will be Zero

All comments

rob_marshall

@CamdenMaggos

Are you by chance using Native Versions (Versions) in this module? I ask because you said Switchover and the only way Switchover works is with Native Versions (basically the versions that come with the system, not a custom versions list). If you are, you might want to play around with formula scope (in the blueprint area to the right) and select Actual Version.

When this is applied, the formula will only be used for "Actual" data, so not Forecast, Plan, or Budget.

See if this helps and if not, report back, especially if I misunderstood the question.

Rob

CamdenMaggos

We only use 3 versions, Actual, Budget, & Forecast.

The issue I am running into is this:

I need to calculate churn rate which is churn / previous month revenue. Our data currently only goes through April (as we have no wrapped up May yet) but when using the function PREVIOUS() it is populating May. This is causing our selection that would typically Calculate YearToDate to be off because it is including that number from May, when I need it to display 0.

So the total formula is taking (Jan-April Churn) / (Jan-May Previous Rev) which is causing the calculation to be incorrect.

ManjunathKN

Hi @CamdenMaggos

Check this formula, let me know if any changes required.

Thanks,
Manjunath

Pooja

Hi,

If I understand your requirement correctly, I think below solution will work.

Create Line item "Switchover+1" and update formula as "IF ISACTUALVERSION() THEN BLANK ELSE ITEM(Time)" and update summary to FIrst non blank.

In CHurn Rate line item update formula as "IF Time Period[SELECT: TIME.'FY22'] + 1 <= ITEM(Time) THEN 0 ELSE Churn / PREVIOUS(Revenue)".

By this rate after switchover date will be Zero

CamdenMaggos

This is excellent, thank you!

CamdenMaggos

Hi Manjunath,

I actually had a very similar formula originally but because IF ISACTUALVERSION() does not cut off the switchover date + 1 month I had to find a way to work around. What I actually ended up doing was a formula that looked like this:

If Revenue = 0 Then 0 Else [Churn / Previous(Revenue)]

This ended up filtering out that extra month

Thank you for your reply!

rob_marshall

@Pooja

While this works, you really shouldn't be using Select statements to members as this is hardcoding. This really becomes a problem when the model rolls to future years. It would be better to use a SYS Time module at the month level, then create a line item to get the year and do lookups on that line item.

Rob

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…