Date Table

Date = 
VAR FirstYear = 2020
VAR FiscalMonth = 4

VAR CalendarDays =
    CALENDAR(DATE(FirstYear,1,1),TODAY())

VAR CalendarStandard = 
    GENERATE(CalendarDays,
        VAR strDay = FORMAT([Date],"DDDD")
        VAR intWeekDay = WEEKDAY([Date])
    RETURN
        ROW(
            "DateKey",FORMAT([Date],"YYYYmmdd"),
            "DayofWeek",WEEKDAY([Date]),
            "WeekDay",strDay,
            "DayofMonth",DAY([Date]),
            "IsWeekend",SWITCH(strday,"Saturday",-1,"Sunday",-1,0),
            "OrdinalDate",DATEDIFF(DATE(YEAR([Date]),1,1),[Date],DAY)+1,
            "WeekStart",[Date]-intWeekDay+1,
            "WeekEnding",[Date]+(7-intWeekDay),
            "WeekofYear",WEEKNUM([Date]),
            "MonthKey",FORMAT([Date],"YYYYMM"),
            "Month",FORMAT([Date],"MMMM"),
            "MonthofYear",MONTH([Date]),
            "QtrofYear",ROUNDUP(MONTH([Date])/3,0)
        )
    )
    
VAR CalendarFiscal = 
    GENERATE(CalendarDays,
        VAR ISOWeek = [Date] + (7-WEEKDAY([Date])-3)
        VAR intMonth = IF(FiscalMonth > 1,FiscalMonth -1,12)
        VAR intPeriod = IF(MONTH(ISOWeek)<=intMonth,MONTH(ISOWeek)+(12-intMonth),MONTH(ISOWeek)-intMonth)
        VAR intYear = IF(MONTH(ISOWeek)<=intMonth,YEAR(ISOWeek),YEAR(ISOWeek)+1)
    RETURN
        ROW(
            "FYName","FY "& IF(FiscalMonth>1,right(intYear-1,2)&"/"&right(intYear,2),intYear),
            "FiscalYear",intYear,
            "FiscalPeriod",intPeriod,
            "FiscalQtr",ROUNDUP(intPeriod/3,0)
        )
    )

Var CalendarComplete = 
        NATURALLEFTOUTERJOIN(CalendarStandard,CalendarFiscal)

VAR Result = 
    SELECTCOLUMNS(CalendarComplete,

        -- Base date columns
        "FULLDATE", [Date],
        "CALENDARYEAR",YEAR([Date]),
        ----DAY
        "DAY_KEY",[DateKey],
        "DAYOFWEEK",[DayofWeek],
        "DAYNAMEOFWEEK",[WeekDay],
        "DAYOFMONTH",[DayofMonth],
        "DAYOFYEAR",[OrdinalDate],
        ----WEEK
        "WEEKDAYWEEKEND",SWITCH([IsWeekend],-1,"Weekend","Weekday"),
        "WEEKSTARTDATE",[WeekStart],
        "WEEKOFYEAR",[WeekofYear],
        ----MONTH
        "MONTH_KEY",[MonthKey],
        "MONTHOFYEAR",[MonthofYear],
        "MONTHNAME",[Month],
        "CALENDARYEARMONTH",YEAR([Date])&"-"&FORMAT([MonthofYear],"00"),
        ----QTR
        "QUARTER_KEY",(YEAR([Date])*10)+[QtrofYear],
        "CALENDARYEARQUARTER",YEAR([Date])&"Q"&FORMAT([QtrofYear],"00"),
        --FISCAL
        "FISCALYEAR",[FiscalYear],
        "FY",[FYName],
        ---PERIOD
        "FISCALPERIOD_KEY",[FiscalYear]&FORMAT([FiscalPeriod],"00"),
        "FISCALMONTH",[FiscalPeriod],
        "FISCALYEARMONTH",[FiscalYear]&"-"&FORMAT([FiscalPeriod],"00"),
        ---QTR
        "FISCALQTR_KEY",([FiscalYear]*10)+[FiscalQtr],        
        "FISCALQUARTER",[FiscalQtr],
        "FISCALYEARQUARTER",[FiscalYear]&"Q"&[FiscalQtr]
)
Return Result
apps-filevi