Master Calendar with offset

[hobanwashburne]

The Master Calendar below seems to work fairly well, but I need to add a Fiscal period which is a 3 month offset from the calendar month (Fiscal Period 201301 = Calendar month 201304 (April)) also our Quarters are also offset Q1 = April thru June. How would I modify the script?

let vMinDate = num(makedate(2006,1,1));
let vMaxDate = num(makedate(2013,12,31));
Let vCurDate = Num(Today());
let vToday = $(vCurDate);

TempCal:
LOAD
date($(vMinDate) + RowNo() - 1 ) as TempDate
AutoGenerate
$(vMaxDate)-$(vMinDate) + 1;

MasterCalendar:
LOAD
TempDate as Date,
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
WeekDay(TempDate) as WeekDay,
'Q' & Ceil(month(TempDate)/3) as Quarter,
Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,
Week(TempDate)& '-' & year(TempDate) as WeekYear,
InYearToDate(TempDate, $(vToday), 0) * -1 as CurYTDFlag,
InYearToDate(TempDate, $(vToday), -1) * -1 as LastYTDFlag
Resident TempCal
Order By TempDate asc;
DROP Table TempCal;

[Clever_Anjos]

Date(addmonths(MonthStart(TempDate),-3), 'MMM-YYYY') as FiscalMonthYear,

[Gysbert_Wassenaar]

Maybe this:

Date(MonthStart(TempDate,-3), 'YYYYMM') as FiscalPeriod,

'Q'&ceil(month(MonthStart(TempDate,-3))/3) as FiscalQuarter

[hobanwashburne]

Any ideas on how to offset the quarters?

[Clever_Anjos]

Apply Gysbert formula to Date(addmonths(MonthStart(TempDate),-3), 'MMM-YYYY')

[hobanwashburne]

I'm still missing something

[Clever_Anjos · Accepted solution]

Date(addmonths(MonthStart(TempDate),-3), 'MMM-YYYY') as FiscalMonthYear,

'Q'&ceil(Month(addmonths(MonthStart(TempDate),-3))/3) as FiscalQuarter