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!
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
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!