I wanted to create a master calendar like below
Reportingperiod , Period Start Date, Period End Date
Nov 2020 - April 2021, 11/01/ 2020 , 04/30/ 2021
based on the below script
vStartdate = Date(MonthStart(AddMonths(Today(),-12)));
Trace $(vStartdate);
vEnddate =Date(MonthEnd(AddMonths(Today(),-1)));
Trace $(vEnddate);
Let varMinDate = Num(vStartdate);
Let varMaxDate = Num(vEnddate);
NoConcatenate
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate,
Monthname($(varMinDate) + IterNo() - 1) as Reporting_Month
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
NoConcatenate
monthcalendar:
Load distinct
Reporting_Month,
MonthStart(TempDate) as monthstart,
Monthend(TempDate) as monthend
Resident TempCalendar;
drop table TempCalendar;
The output shows Reporting month, month start , month end for last 12 months (may 2020 to April 2021)
I wanted to dynamically show the last 6 month as reporting period (Nov 2020 - April 2021) as reporting period column and period start date column as 11/01/ 2020 and Period end date column as 04/30/ 2021, similarly the previous 6 months starting from (may 2020 - oct 2020) as reporting period column with period start date as 05/01/2020 and period end date as 10/30/2020, any insights on this is appreciated
