Recently, I want to create a fiscal rolling month calendar for PY and CY.
like the below screenshot. I have written the fiscal month calendar. How to write the rolling month calendar . It's too difficult. Could you help me out? Thank you so much.
let vMinDate=num(Date('2021-10-01'));
let vMaxDate=num(Date('2023-09-30'));
Master_Calendar_Temp:
LOAD
Date(TempDate) as "FROZENDATE",
if(YearToDate(TempDate,0, 10) or YearToDate(date(num(TempDate)+1),-1, 10),'YTD') as FYTD,
'P'&Month(AddMonths (Date(TempDate,'MM/dd/yyyy'),3))*1 as Fmonth,
if (date(TempDate)>='10/1/2022', 'CY','PY') as FY;
LOAD
$(vMinDate) + IterNo() - 1 as TempDate
Autogenerate 1 While ($(vMinDate) + IterNo() - 1) <= $(vMaxDate);
CrossTable(Flag, Month)
LOAD FROZENDATE, FYTD, Fmonth Resident Master_Calendar_Temp;
DROP Field Flag;
