Native YTD Calculation issue

kristenr
kristenr
in Modeling

Hi,

I have various forecast versions and a prior year version. I want to do a comparison between the selected version and the prior year version. I am finding that the YTD time summary for the prior year version is not taking the YTD as determined by the selected version.

image.png

You can see that the YTD calculation for Current Year line item changes based on the version selected but not the Prior Year.

image.png

Any guidance would be greatly appreciated!

Tagged:

Answers

  • Badam
    Badam

    Hi @kristenr ,

    I could see that you have used select for the prior year line item, so irrespective any version dimensions that are there in the model, for all versions it will show the same data from the above formula.

    If you need any specific guidance on the above please feel free to reach out to me.

    Thanks

    Badam

    Matasma

  • vanreign
    vanreign

    @kristenr

    Hi Kristen,

    A few things may help with simplifying this. The Applies to is not presented above so making some assumptions. I am arbitrarily giving the amount name of Revenue $ and renaming these. There are more than one way to do this but this will work:

    Current Year Revenue $: Revenue $.Amount[LOOKUP: Time Mgt.Current Year]

    Prior Year Revenue $ Revenue $.Amount[LOOKUP: Time Mgt.Prior Year]

    (+/-) in Revenue from Prior Year $

    (+/-) in Revenue from Prior Year %

    Revenue $ Module:

    1/22 2/22 3/22…..YTD 1/23 2/23 3/23….YTD

    Revenue $

    LIS Revenue $ (Revenue $ Module):

    Revenue $ X

    Time System Module:

    Current Year = 2023

    Prior Year = Current Year - 1

    I hope this helps.

    Cheers,

    James

Categories