New Contributor

Need formula help to calculate YEARTODATE -1

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

11 REPLIES 11
Community Boss

Re: Need formula help to calculate YEARTODATE -1

@phanishashank ,

 

What about creating a new line item with the formula: previous(year to date line item).

 

Does this help?

 

Rob

New Contributor

Re: Need formula help to calculate YEARTODATE -1

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.

Highlighted
Community Boss

Re: Need formula help to calculate YEARTODATE -1

@phanishashank ,

I am confused, are you wanting the previous months YearToDate value or are you wanting to subtract 1 from the year to date value?

New Contributor

Re: Need formula help to calculate YEARTODATE -1

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

Community Boss

Re: Need formula help to calculate YEARTODATE -1

@phanishashank ,

 

Ok, I think I understand, let's see if this works for you.

 

Create a module (I called it SYS Global) where it has two line items (Current Period and Current Period -1).  My models' current period is Jan 18.

 

2019-08-22_17-55-45.png

 

2019-08-22_17-52-57.png

 

In your module with transactional data, create your YearToDate formula and YearToDate - 1.  But on the on the YearToDate -1, remove the TimeScale (set it to Not Applicable) - this saves space because you don't want it changing everything month that is toggled.  The YearToDate - 1 formula will be: Year to Date[LOOKUP: SYS Global.'Current Period -1']

 

2019-08-22_17-54-41.png

 

2019-08-22_17-54-11.png

 

Is that what you are asking for?

 

Rob

 

Community Boss

Re: Need formula help to calculate YEARTODATE -1

Hi,

 

Subtract current month value from YTD value.

Try this,

Demand[SELECT: TIME.YTD] - Demand[LOOKUP: Settings Mod.Current Month]

Current Month - Aug 19, 

YTD value is 800 and YTD-1 value is 700

1.png2.png

 

Also, check this link, it as a similar requirement

https://community.anaplan.com/t5/Anaplan-Platform-Discussions/Find-YTD-excluding-current-month-and-f...

 

Regards,

Vignesh M

New Contributor

Re: Need formula help to calculate YEARTODATE -1

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.

 

 

 

Community Boss

Re: Need formula help to calculate YEARTODATE -1

@phanishashank ,

 

Please be careful in using TimeSum as TimeSum is really only supposed to be used when you are not using Time Scale in the module as TimeSum may have performance issues, especially with models at scale.  Also, in your formula, you should hardcode the parameters as next year you will have to go through and remember which line items will need to be updated.

 

Thanks,

Rob

New Contributor

Re: Need formula help to calculate YEARTODATE -1

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.