Previous Function Cut-Off Date

CamdenMaggos
Occasional Contributor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Pooja
Contributor

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)".

Pooja_0-1652438789708.png

 

 

 

 

 

By this rate after switchover date will be Zero

 

View solution in original post

7 REPLIES 7
rob_marshall
Moderator

@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.

2022-05-12_15-32-34.png

 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
Occasional Contributor

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
Super Contributor

Hi @CamdenMaggos 

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

ManjunathKN_0-1652411921966.png

Thanks,
Manjunath

CamdenMaggos
Occasional Contributor

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!

Pooja
Contributor

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)".

Pooja_0-1652438789708.png

 

 

 

 

 

By this rate after switchover date will be Zero

 

CamdenMaggos
Occasional Contributor

This is excellent, thank you!

rob_marshall
Moderator

@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