Very useful discussion. I am leveraging this to create a fiscal calender that starts in october (starts on saturday for the week whose monday is first monday of October).
I am using below script to adjust the weeks. So fiscal week 1 in 2012 starts on Sat, 1st Oct 2011 and goes on.
right (WeekName(TempDate,13,-2),2) as FiscalWeek
if(Right(WeekName(TempDate,13,-2),2)>0 and Right(WeekName(TempDate,13,-2),2)<=13,YearName(TempDate,1),YearName(TempDate,0)) as FiscalYear,
Weeks are working perfect but I am facing problem in scripting years properly. I am incrementing year with the script above (logic: for first 13 quarters increment calender year by 1 to get fiscal year and after that use calender year as fiscal year). This does not apply to certain exceptions like 12/31/2012 (attached screenshot) as its in fiscal week 14 and calender year is still 2011 not 2012.
My question is how to handle years when starting fiscal calender different than calender year?
Thanks for helping with this. I very much appreciate it.