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

Best Answer

  • Pooja
    Answer ✓

    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

     

Answers

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

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

  • Hi @CamdenMaggos 

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

    ManjunathKN_0-1652411921966.png

    Thanks,
    Manjunath

  • This is excellent, thank you!

  • 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 

     

    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