Power Query M - Code for creating a table DCalendar (Fiscal)

Code for creating a table Dim_Calendar Refresh

Path
let
    Source = "C:\Temp\datasets\board\csv\__20200210_120926_.ARCHIVE"
in
    Source
========================================
dCalend_FiscalYearEndMonthValue
let
    Source = 12
in
    Source
========================================
fnGetFiscalYear
let
    Source = (AnyDate as date) =>
    let
    FiscalYearStartingMonth = if dCalend_FiscalYearEndMonthValue = 12 then 1 else dCalend_FiscalYearEndMonthValue + 1,
    CurrentDate = AnyDate,
    CurrentMonth = Date.Month(CurrentDate),
    CurrentYear = Date.Year(CurrentDate),
    CurrentFiscalYear =
    if dCalend_FiscalYearEndMonthValue = 12 then CurrentYear else
    if CurrentMonth < FiscalYearStartingMonth then CurrentYear else
    CurrentYear + 1
    in
    CurrentFiscalYear
in
    Source
========================================
fnGetFiscalMonthYear
let
    Source = (AnyDate as date, month as number) =>
    let
    FiscalYearStartingMonth = if dCalend_FiscalYearEndMonthValue = 12 then 1 else dCalend_FiscalYearEndMonthValue + 1,
    CurrentDate = AnyDate,
    CurrentMonth = Date.Month(CurrentDate),
    CurrentYear = Date.Year(CurrentDate),
    CurrentQuarter = Date.QuarterOfYear(CurrentDate),
    CurrentFiscalYear = fnGetFiscalYear(CurrentDate),
    CurrentFiscalMonthYear =
    if dCalend_FiscalYearEndMonthValue = 12 then Text.From(CurrentFiscalYear*100 + CurrentMonth) else
    if CurrentMonth < FiscalYearStartingMonth then Text.From(CurrentFiscalYear*100 + 12 - month + CurrentMonth) else
    Text.From(CurrentFiscalYear*100 + CurrentMonth - month)
    in
    CurrentFiscalMonthYear
in
    Source
========================================
fnGetFiscalQuarterYear
let
    Source = (AnyDate as date, month as number) =>
    let
    FiscalYearStartingMonth = if dCalend_FiscalYearEndMonthValue = 12 then 1 else dCalend_FiscalYearEndMonthValue + 1,
    CurrentDate = AnyDate,
    CurrentMonth = Date.Month(CurrentDate),
    CurrentYear = Date.Year(CurrentDate),
    CurrentQuarter = Date.QuarterOfYear(CurrentDate),
    CurrentFiscalYear = fnGetFiscalYear(CurrentDate),
    CurrentFiscalQuarterYear =
    if dCalend_FiscalYearEndMonthValue = 12 then "Q" & Text.From(CurrentQuarter) & "-" & Text.From(CurrentFiscalYear) else
    if CurrentMonth < FiscalYearStartingMonth then "Q" & Text.From(Number.RoundUp((12 - month + CurrentMonth)/3)) & "-" & Text.From(CurrentFiscalYear) else
    "Q" & Text.From(Number.RoundUp((CurrentMonth - month)/3)) & "-" & Text.From(CurrentFiscalYear)
    in
    CurrentFiscalQuarterYear
in
    Source
========================================
fnGetPastDate
let
    Source = (numberOfDays as number) =>
let
    CurrentDate = DateTime.LocalNow(),
    RequiredDate = DateTimeZone.From(Date.AddDays(CurrentDate, numberOfDays * -1))
in
    RequiredDate
in
    Source
========================================
fnDateTable
let
    Source = (month as number) =>
let
    FiscalYearStartingMonth = if dCalend_FiscalYearEndMonthValue = 12 then 1 else dCalend_FiscalYearEndMonthValue + 1,
 
    CurrentDate = Date.FromText("2019-07-31"), //Ajustar para Produção
    //CurrentDate = Date.From(DateTime.LocalNow()),
    CurrentFiscalYear = fnGetFiscalYear(CurrentDate),
 
    FiscalStartDate = Date.From(Text.From(FiscalYearStartingMonth) & "/01/" & Text.From(CurrentFiscalYear)),
 
    StartDate = Date.From(Date.AddYears(FiscalStartDate,-1)), //Inicio do Calendario
    EndDate= Date.From(Date.AddYears(FiscalStartDate, 1)),    //Fim do Calendario

    //Create lists of month and day names for use later on
    MonthList = {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"},
    DayList = {"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"},

    //Find the number of days between the end date and the start date
    NumberOfDates = Duration.Days(EndDate-StartDate)+1,

    //Generate a continuous list of dates from the start date to the end date
    DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),

    //Turn this list into a table
    TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),

    //Caste the single column in the table to type date
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),

    //Add custom columns for day of month, month number, year
    DayOfMonth       = Table.AddColumn(ChangedType, "DayOfMonth", each Date.Day([Date])),
    MonthNumber      = Table.AddColumn(DayOfMonth, "MonthNumberOfYear", each Date.Month([Date])),
    Year             = Table.AddColumn(MonthNumber, "Year", each Date.Year([Date])),
    DayOfWeekNumber  = Table.AddColumn(Year, "DayOfWeekNumber", each Date.DayOfWeek([Date])+1),
    QuarterOfYear    = Table.AddColumn(DayOfWeekNumber, "QuarterOfYear", each "Q" & Text.From(Date.QuarterOfYear([Date])) & "-" & Text.From([Year])),
    YYYYMMDD         = Table.AddColumn(QuarterOfYear, "YYYYMMDD", each Text.From(Date.Year([Date]))  & "" & Text.PadStart(Text.From(Date.Month([Date])), 2, "0")  & "" & Text.PadStart(Text.From(Date.Day([Date])), 2, "0")),
    YYYYMM           = Table.AddColumn(YYYYMMDD, "YYYYMM", each Text.From(Date.Year([Date]))  & "" & Text.PadStart(Text.From(Date.Month([Date])), 2, "0")  ),

    // Add Fiscal Period values for FiscalYear, FiscalQuarter, FiscalMonth
    FiscalYearAdded = Table.AddColumn(YYYYMM, "FiscalYear", each fnGetFiscalYear([Date])),
    FiscalQuarterYearAdded = Table.AddColumn(FiscalYearAdded, "FiscalQuarterYear", each fnGetFiscalQuarterYear([Date], month)),
    FiscalMonthYearAdded = Table.AddColumn(FiscalQuarterYearAdded, "FiscalMonthYear", each fnGetFiscalMonthYear([Date], month)),

    //Since Power Query doesn't have functions to return day or month names, use the lists created earlier for this
    MonthName = Table.AddColumn(FiscalMonthYearAdded, "MonthName", each MonthList{[MonthNumberOfYear]-1}),
    DayName = Table.AddColumn(MonthName, "DayName", each DayList{[DayOfWeekNumber]-1}),

    //use the System Date to determine Today
    IsToday = Table.AddColumn(DayName, "IsToday", each Date.IsInCurrentDay([Date])),
    WeekEnding = Table.AddColumn(IsToday, "Week Ending", each Date.EndOfWeek([Date])),
    WeekStarting = Table.AddColumn(WeekEnding, "Week Starting", each Date.StartOfWeek([Date])),

    //Group Dates into bands of Last7Days, Last30Days, Last90Days, Last180Days, Last360Days
    TodayFunction = DateTime.FixedLocalNow,
    Today = Table.AddColumn(WeekEnding, "Today", each TodayFunction()),

    CurrentFiscalYearFunction = fnGetFiscalYear,
    CurrentFiscalYearColumn = Table.AddColumn(Today, "CurrentFiscalYear", each CurrentFiscalYearFunction(CurrentDate)),

    CurrentFiscalMonthYearFunction = fnGetFiscalMonthYear,
    CurrentFiscalMonthYearColumn = Table.AddColumn(CurrentFiscalYearColumn, "CurrentFiscalMonthYear", each CurrentFiscalMonthYearFunction(CurrentDate,month)),

    CurrentFiscalQuarterYearFunction = fnGetFiscalQuarterYear,
    CurrentFiscalQuarterYearColumn = Table.AddColumn(CurrentFiscalMonthYearColumn , "CurrentFiscalQuarterYear", each CurrentFiscalQuarterYearFunction(CurrentDate,month)),

    WeekNumberOfYear= Table.AddColumn(CurrentFiscalQuarterYearColumn, "WeekNumberOfYear", each Text.From(Date.WeekOfYear([Date]))&"-"&Text.From([Year])),

    #"Changed Type3" = Table.TransformColumnTypes(WeekNumberOfYear,{{"Today", Date.Type}, {"DayOfMonth", Int64.Type}, {"MonthNumberOfYear", Int64.Type}, {"Year", Int64.Type}, {"DayOfWeekNumber", Int64.Type}, {"FiscalYear", Int64.Type}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type3", "Date", "DateValue"),

    #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column1", {{"DateValue", Int64.Type}, {"Today", Int64.Type}}),

    #"Added L4LJanMonthAtual" = Table.AddColumn(#"Changed Type2", "L4LJanMonthAtual", each if [DateValue] <= [CurrentDate] then if [DateValue] >= [CurrentDate]-6 then 1 else 0 else 0),
    #"Added L4LJanMonthOld" = Table.AddColumn(#"Added L4LJanMonthAtual", "L4LJanMonthOld", each if [DateValue] <= [CurrentDate] then if [DateValue] >= [CurrentDate]-6 then 1 else 0 else 0),
    #"Added L4LJanYearAtual" = Table.AddColumn(#"Added L4LJanMonthOld", "L4LJanYearAtual", each if [DateValue] <= [CurrentDate] then if [DateValue] >= [CurrentDate]-6 then 1 else 0 else 0),
    #"Added L4LJanYearOld" = Table.AddColumn(#"Added L4LJanYearAtual", "L4LJanYearOld", each if [DateValue] <= [CurrentDate] then if [DateValue] >= [CurrentDate]-6 then 1 else 0 else 0),

    #"Added IsLast7Days" = Table.AddColumn(#"Changed Type2", "IsLast7Days", each if [DateValue] <= [Today] then if [DateValue] >= [Today]-6 then 1 else 0 else 0),
    #"Added IsLast30Days" = Table.AddColumn(#"Added IsLast7Days", "IsLast30Days", each if [DateValue] <= [Today] then if [DateValue] >= [Today]-29 then 1 else 0 else 0),
    #"Added IsLast90Days" = Table.AddColumn(#"Added IsLast30Days", "IsLast90Days", each if [DateValue] <= [Today] then if [DateValue] >= [Today]-89 then 1 else 0 else 0),
    #"Added IsLast180Days" = Table.AddColumn(#"Added IsLast90Days", "IsLast180Days", each if [DateValue] <= [Today] then if [DateValue] >= [Today]-179 then 1 else 0 else 0),
    #"Added IsLast365Days" = Table.AddColumn(#"Added IsLast180Days", "IsLast365Days", each if [DateValue] <= [Today] then if [DateValue] >= [Today]-364 then 1 else 0 else 0),
    #"Added IsNext90Days" = Table.AddColumn(#"Added IsLast365Days", "IsNext90Days", each if [DateValue] >= [Today] then if [DateValue] <= [Today]+90 then 1 else 0 else 0),
    #"Added IsCurrentFiscalQuarter" = Table.AddColumn(#"Added IsNext90Days", "IsCurrentFiscalQuarter", each if [FiscalQuarterYear] = [CurrentFiscalQuarterYear] then 1 else 0),
    #"Added IsCurrentFiscalMonth" = Table.AddColumn(#"Added IsCurrentFiscalQuarter", "IsCurrentFiscalMonth", each if [FiscalMonthYear] = [CurrentFiscalMonthYear] then 1 else 0),
    #"Added IsCurrentFiscalYear" = Table.AddColumn(#"Added IsCurrentFiscalMonth", "IsCurrentFiscalYear", each if [FiscalYear] = [CurrentFiscalYear] then 1 else 0),
    #"Added IsCurrentWeek" = Table.AddColumn(#"Added IsCurrentFiscalYear", "IsCurrentWeek", each if Date.IsInCurrentWeek([Date]) then 1 else 0),
    #"Added IsCurrentMonth" = Table.AddColumn(#"Added IsCurrentWeek", "IsCurrentMonth", each if Date.IsInCurrentMonth([Date]) then 1 else 0),
    #"Changed BinaryToLogical" = Table.TransformColumnTypes(#"Added IsCurrentMonth",{
        {"IsNext90Days", type logical}, {"IsLast7Days", type logical}, {"IsLast30Days", type logical},
        {"IsLast90Days", type logical}, {"IsLast180Days", type logical}, {"IsLast365Days", type logical},
        {"IsCurrentFiscalQuarter", type logical},{"IsCurrentFiscalYear", type logical}, {"IsCurrentFiscalMonth", type logical}, {"IsCurrentWeek", type logical}, {"IsCurrentMonth", type logical}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed BinaryToLogical",{"Today", "DateValue","CurrentFiscalYear","CurrentFiscalMonthYear","CurrentFiscalQuarterYear"})
in
    #"Removed Columns"
in
    Source
========================================
DIM_Calendar
//dCalendarioN:
let
    Source = fnDateTable(dCalend_FiscalYearEndMonthValue),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week Ending", type date}}),
    #"Added Month-Year" = Table.AddColumn(#"Changed Type", "Month-Year", each Text.Start([MonthName],3)&"-"&Text.From([Year])),
    #"Added MonthYearSortBy" = Table.AddColumn(#"Added Month-Year", "MonthYearSortBy", each [Year]*100+[MonthNumberOfYear]),
    #"Lang - Renamed Columns" = Table.RenameColumns(#"Added MonthYearSortBy",{
    {"DayOfMonth","Day Of Month"},
    {"MonthNumberOfYear","Month Number Of Year"},
    {"DayOfWeekNumber","Day Number Of Week"},
    {"MonthName","Month Name"},
    {"DayName","Day Name"},
    {"IsToday","Is Today"},
    {"WeekNumberOfYear","Week Number Of Year"},
    {"IsLast7Days","Is Last 7 Days"},
    {"IsLast30Days","Is Last 30 Days"},
    {"IsLast90Days","Is Last 90 Days"},
    {"IsLast180Days","Is Last 180 Days"},
    {"IsLast365Days","Is Last 365 Days"},
    {"IsNext90Days","Is Next 90 Days"},
    {"Month-Year","Month Year"},
    {"MonthYearSortBy","Month Year (Number)"},
    {"QuarterOfYear","Quarter Of Year"},
    {"FiscalYear","Fiscal Year"},
    {"IsCurrentFiscalMonth","Is Current Fiscal Month"},
    {"IsCurrentFiscalQuarter","Is Current Fiscal Quarter"},
    {"IsCurrentFiscalYear", "Is Current Fiscal Year"},
    {"FiscalQuarterYear","Fiscal Quarter Year"},
    {"FiscalMonthYear","Fiscal Month Year"},
    {"IsCurrentWeek","Is Current Week"},
    {"IsCurrentMonth","Is Current Month"}
    }),
    #"Sorted Rows" = Table.Sort(#"Lang - Renamed Columns",{{"Date", Order.Descending}})
in
    #"Sorted Rows"

========================================
dCalendario_LastRefresh
let
    Source = #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}})
in
    Source