Need formula help to calculate YEARTODATE -1

phanishashank

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

rob_marshall

@phanishashank ,

 

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

 

Does this help?

 

Rob

phanishashank

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.

rob_marshall

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

phanishashank

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

rob_marshall

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

 

 

 

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']

 

 

 

Is that what you are asking for?

 

Rob

 

VIGNESH.M

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

 

Also, check this link, it as a similar requirement

https://community.anaplan.com/t5/Anaplan-Platform-Discussions/Find-YTD-excluding-current-month-and-find-YTG-including-current/m-p/48890#M6653

 

Regards,

Vignesh M

phanishashank

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.

 

 

 

rob_marshall

@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

phanishashank

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.

VIGNESH.M

@phanishashank , Try my above solution. You can achieve the result without TIMESUM.

 

~Vignesh M

rob_marshall

@phanishashank ,

 

Yea, try either @VIGNESH.M solution or mine, I believe they both get you the same answer.

 

Rob