YEAR() function not working

PrevContributor
PrevContributor
in Modeling

My Fin Year item is set to Time Period format: "Year". Our financial year setting is April to March. Attached is the Anaplan example and my working model that seems to return different result. I would expect that in calendar month/year, May 2016, that the Financial Year time period would say FY17 (appears that is the case).  I would then expect that using the Year() function on Fin Year item, that it would return 2017.   It does not. I can easly fix this by using +1, however I believe that should not be necessary in the way that I read how this function works.  The only difference from the example is that I have timescale (months) in the module. Anyone experience similar issue or know why it may return the result that I'm seeing? Regards Devin

Tagged:

Answers

  • peter_mcanena
    peter_mcanena
    There are two possible causes of this discrepancy. I can't tell which is your problem without knowing how your model's set up but check both.

    First of all, the year names FY16, FY17 etc. depend on the "Fiscal Year label is aligned with" setting, under Time. If you select End Month of the Fiscal Year then FY17 is Apr 2016 to Mar 2017; but if you select Start Month of the Fiscal Year then FY17 is Apr 2017 to Mar 2018. The Anaplan example may have a different setting from your model. In this case, you just need to add 1 to your calculation:
    TEXT(YEAR(Fin Year) + 1)
    The other possibility is to be explicit about whether you want the year number from the start or the end of the financial year. You can do this by adding the START() or END() function to your existing formula:
    TEXT(YEAR(START(Fin Year)))
    or
    TEXT(YEAR(END(Fin Year)))
    One of these methods should give you what you want.

Categories