Previous Function Cut-Off Date

CamdenMaggos

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

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

 

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

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