If I read you correctly you are part right. I do need to change it to
intnx('year.10', intnx('week.1', dt,1),1) since Sunday is the first day, but the week.1 must be inner. Or maybe that is what you meant.
However, I would expect a given date would already be within the fiscal year/month which would mean that the inner INTNX would decrement, not increment. Again, it would help if you ran the code for self-diagnosis and desk-checking - hopefully the sample code provided helps.
I discovered the underlying problem. I was told the FY started the first Sunday in October, but they actually calculate it on a 5-4-4 calendar. This means a table lookup is the only option.
Luckily the date table they have is indexed so it works well.
I work with clients who have a fiscal-year application and we use a PROC FORMAT to derive the following:
- fiscal-year start date (maintained 5 years past and future for 3 years).
- fiscal-year "period" - format: yyyy/pp, where "pp" could be 01 to 13 - a character var.
- fiscal-year day-of-year (like Julian date but for the fiscal-year relevance).
- fiscal-year week-of-year (like ISO week of year).
You can create one format that returns each of the above values as a sub-field, parsed with the SCAN function.