Fiscal and Standard Calendar generation

    Hi All,

    What is Fiscal Calendar?

    From Wikipedia:

    A fiscal year (or financial year, or sometimes budget year) is a period used for calculating annual ("yearly") financial statements in businesses and other organizations.

    Fiscal years vary between businesses and countries. The "fiscal year" may also refer to the year used for income tax reporting.


    In India, the government's financial year runs from 1 April to 31 March midnight.

    Example: 1 April 2014 to 31 March 2015 for the financial year 2014–2015. It is also abbreviated as FY15.


    The United States federal government's fiscal year is the 12-month period ending on 30 September of that year, having begun on 1 October of the previous calendar year. In particular, the identification of a fiscal year is the calendar year in which it ends; thus, the current fiscal year is 2014, often written as "FY2014" or "FY14", which began on 1 October 2013 and which will end on 30 September 2014.


    Below script helps in generating the Standard and Fiscal Year Calendar generation, for this you need to configure 3 variables,


    vFiscalYearStartMonth - Tells the starting month of the Fiscal Year

    vStartDate - Starting date of the Calendar generation

    vEndDate - Ending date of the Calendar generation




    SET vFiscalYearStartMonth = 4;

    LET vStartDate = Num(YearStart(Today(), -1));

    LET vEndDate = Num(YearEnd(Today()));





    Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

    Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name



    Year(Date) AS Year, // Standard Calendar Year

    Month(Date) AS Month, // Standard Calendar Month

    Date(MonthEnd(Date), 'MMM') AS MonthName,  // Standard Calendar Month Name

    Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter,  // Standard Calendar Quarter

    Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month

    YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear;  // Fiscal Calendar Year


    Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

    RangeSum(Peek('RowNum'), 1) AS RowNum

    AutoGenerate vEndDate - vStartDate + 1;


    Note: Based on your requirement you can modify the script to arrive new fields like Week, YTD, MTD etc.