Need formula help to calculate YEARTODATE -1

Options
phanishashank
phanishashank Member, ALL USERS Posts: 5 New Contributor
in Planning & Modeling

Hi,

I have a need to calculate YTD Actuals but the amount should show the sum of values from start month (Jan 19) to One month prior to the current period i.e (July 19).

 

The current formula I have is this: YEARTODATE('P&L by Cost Center'.Amount Adj[LOOKUP: Version Selector.Actuals])

I need to pull YEARTODATE just until end of last month which is in my case end of July 19.

 

Let me know.

 

Thanks

Phani

Tagged:

Comments

  • phanishashank
    phanishashank Member, ALL USERS Posts: 5 New Contributor

    I thought but didn't try as Previous goes the time period of the cell. I want it to always do Yeartodate -1.

     

    But will give it a try.

  • phanishashank
    phanishashank Member, ALL USERS Posts: 5 New Contributor

    Gave it a try and it didn't  work.

     

    But what I am looking for, any month I toggle to it should default value to Yeartodate of CurrentMonth-1

     

    The current month in our system is Aug 19. So I want for all periods, to show the same value "Yeartodate until Jul 19".

  • phanishashank
    phanishashank Member, ALL USERS Posts: 5 New Contributor

    Finally, got it. Thank you all for the wonderful ideas!! Really appreciate it.

     

    Here is what I had to do:

     

    Create a line item in the source module: Actuals Jan to Prior

    1. IF YEAR(ITEM(Time)) = 2018 THEN Actual Amount Adj ELSE TIMESUM(Actual Amount Adj, TIME.'Jan 19', Time Reference.Prior Month)

     

    Then in the target module added this:

    2. 'P&L by Cost Center'.Actuals Jan to Prior[LOOKUP: Version Selector.Actuals]

     

    I was trying to do this in one step on the target module only and couldn't get it. So for now this approach gives what we want.

     

     

     

  • phanishashank
    phanishashank Member, ALL USERS Posts: 5 New Contributor

    Hmm.. I didn't about that issue with Timesum. Any idea to get this done without timesum then?

     

    Regarding hardcoding, I will change it to be parameter driven.

Categories