Displaying the first date of my historical years

Hi All,

I was wondering if there is a syntax that gives me the first date of my historical years, based on the timescale settings. My Fiscal Year (currently) goes from 01-06-2018 to 31-05-2019 and I have 2 past years within my model, i.e. the date I'm looking for right now is 01-06-2016. I would like to have a syntax that gives me this date based on the timescale, because it has to 'move' to the next year once we go into the new Fiscal Year.

Currently this date is a manual input (see attachment).Additionally, almost al other dates are a function of this 'First Actual Date', so I do not want to have the 'First Actual Date' to be a function of the other dates.

hope this is clear enough and looking forward to answers!

Best,

Thomas

Dates_44708.PNG

Accepted answers

DavidSmith

Yep - This is possible (as easy!!)

1. Create a check of the first period

2. Return the date if 1. above

In the example below, I have weeks

the formulas are:

1st Week? - OFFSET(1, -1, 0) = 0 (formatted as Boolean)

Date = IF NOT '1st Week?' THEN BLANK ELSE START() (formatted as Date)

3. Bring the value into your target module (formatted as date)

The formula is

Date - TIMESUM(Future Time by Week.Date) (formatted as Date)

You can also use that the trick in 1. for the end of the timescale by changing the -1 parameter

OFFSET(1, 1, 0) = 0

Hope that helps

David

All comments

DavidSmith

Yep - This is possible (as easy!!)

1. Create a check of the first period

2. Return the date if 1. above

In the example below, I have weeks

the formulas are:

1st Week? - OFFSET(1, -1, 0) = 0 (formatted as Boolean)

Date = IF NOT '1st Week?' THEN BLANK ELSE START() (formatted as Date)

3. Bring the value into your target module (formatted as date)

The formula is

Date - TIMESUM(Future Time by Week.Date) (formatted as Date)

You can also use that the trick in 1. for the end of the timescale by changing the -1 parameter

OFFSET(1, 1, 0) = 0

Hope that helps

David

thbijkerk

Great, this helps indeed!

Thought there would be an easy solution, but just could not come up with it yet.

Thanks!

DavidSmith

I use the 1st period flag to get around having an additional line item for opening balances too

IF 1st Period THEN Opening Balance Data ELSE PREVIOUS(Closing)

Where the Opening Balance Data is not dimensioned by time

David

thbijkerk

Nice work-around too. I'm using flags a lot as well in the financial models. Like the flexibility of working with it.

DavidSmith

Booleans are great and the most efficient method of modelling.

Just don't do IF xxx = yyy THEN TRUE ELSE FALSE

My pet hate!!!

xxx = yyy is sufficient!

David

erik.bangsund

Would you explain how that OFFSET is accomplishing the task? I have only used OFFSET with a specific Line Item. What does giving it a '1' do?

DavidSmith

@erik.bangsund

The offset 1 is pulling '1' in from the previous period. It could be any value, I just chose 1 but by embedding the absolute value within the formula, it saves having another line item

Because you use the 0 as the "fill" parameter to display a value if Offset doesn't return a value, you can check for that. In the first period, there is no previous period, the formula returns the fill value, so you check for that

I could have written OFFSET(99,-1,66) = 66. The result would be the same

I hope that helps

David

erik.bangsund

Ah! So your OFFSET is asking "is there something in the period before this period?" And this will always give you some sort of value EXCEPT when you are in the first period of the timescale. In that one case it will not find anything so it will return whatever you put as the third term. You can then ask "did I get the third term value?" You will only get that return when you are in the first period of the timescale. Nice!

DavidSmith

Exactly

And, of course, the reverse is true for the last period as there is nothing in the "next" period.

Simple!

David

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…