// dyota 2024-08-05
// this table includes a week number, and a financial year week number, to do week-to-week comparisons

let
    startYear = 2021,
    endYear = Date.Year(DateTime.LocalNow()) + 2,

    years = {startYear .. endYear},

    start = #date(startYear, 07, 01),
    end = #date(endYear, 06, 30),
    FiscalYearEndMonth = 6,

    TodaysDate = Date.From(DateTime.LocalNow()),

    CurrentFiscalYear = if Date.Month(DateTime.LocalNow()) <= 6 then Date.Year(DateTime.LocalNow()) else Date.Year(DateTime.LocalNow()) + 1,
    CurrentEOM = Date.From(Date.EndOfMonth(DateTime.LocalNow())),
    
    // "+1" included so that it will include the very last day (i.e. 30th June of the last year)
    Source = List.Dates(start, Duration.Days(end-start) + 1, #duration(1, 0, 0, 0)),

    tableAddColumns = (source as table, columnsToAdd as list) as table => 
        List.Accumulate(
            columnsToAdd,
            source,
            (base as table, thisColumn as list) => 
                Table.AddColumn(
                    base, 
                    thisColumn{0},
                    thisColumn{1},
                    try thisColumn{2} otherwise null
                )
            ),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed to DateFormat" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    
    #"Added Index" =            Table.AddIndexColumn(#"Changed to DateFormat", "Index", 1, 1, Int64.Type),

    q1 = {7..9},
    q2 = {10..12},
    q3 = {1..3},
    q4 = {4..6},
    h1 = q1 & q2,
    h2 = q3 & q4,

    addColumns = tableAddColumns(
        #"Added Index", 
        {
            // break down into numbers
            { "Day", each Date.Day([Date]), Int8.Type }, 
            { "Week Number", each Date.WeekOfYear([Date]), Int16.Type },
            { "Calendar Month", each Date.Month([Date]), Int64.Type }, 
            { "Calendar Quarter", each Date.QuarterOfYear([Date]), Int64.Type }, 
            { "Calendar Quarter Label", each "Q" & Text.From([Calendar Quarter]), type text }, 
            { "Calendar Year", each Date.Year([Date]), Int64.Type }, 

            // names
            { "Weekday", each Date.DayOfWeek([Date],Day.Monday), Int64.Type },
            { "DayName", each Date.ToText([Date],"ddd"), type text }, 
            { "Month Name", each Date.ToText([Date],"MMMM"), type text }, 
            { "Short Month Name", each Text.Start([Month Name], 3), type text }, 
            
            // surrounding dates
            { "Start Of Week", each Date.StartOfWeek([Date],Day.Monday), type date },
            { "End Of Week", each Date.EndOfWeek([Date]), type date }, 
            { "End Of Week Offset", each ([End Of Week] - Date.EndOfWeek(TodaysDate))/7, type duration },
            { "Start of Month", each Date.StartOfMonth([Date]), type date }, 
            { "End of Month", each Date.EndOfMonth([Date]), type date }, 

            // text formats
            { "DD-MMM-YY", each Date.ToText([Date],"d-MMM-yy"), type text }, 
            { "Week Ending", each "w/e " & Date.ToText([End Of Week],[Format="d-MMM-yy", Culture="en-AU"]), type text },
            { "YearMonthDay", each Date.ToText([Date], "yyyyMMdd"), type text }, 
            { "YearMonth", each Date.ToText([Date], "yyyyMM"), type text }, 

            // fiscal year
            { "Fiscal Month", each if [Calendar Month] <= FiscalYearEndMonth then [Calendar Month] + FiscalYearEndMonth else [Calendar Month] - FiscalYearEndMonth, Int8.Type }, 
            { "Fiscal Quarter", each if List.Contains(h1, [Calendar Month]) then [Calendar Quarter] - 2 else [Calendar Quarter] + 2 , Int64.Type },
            { "Fiscal Quarter Label", each "Q" & Text.From([Fiscal Quarter]), type text }, 
            { "Fiscal Year", each if [Fiscal Month] > FiscalYearEndMonth then [Calendar Year] else [Calendar Year] + 1, Int16.Type }, 
            { "Fiscal Year Label", each "FY" & Text.End(Text.From([Fiscal Year]), 2), type text }, 
            { "MMM-YY", each Date.ToText([Date],"MMM-yy"), type text }, 
            {
                "Fiscal Year (Week Basis)", 
                each 
                    let
                        month = Date.Month([Start Of Week]),
                        year = Date.Year([Start Of Week])
                    in
                        if (List.Contains(h1, month)) then year + 1 else year,
                Int16.Type
            }, 

            { "FiscalYearOffset", each [Fiscal Year] - CurrentFiscalYear, Int8.Type }, 

            { "MonthWeek", each Number.ToText(Date.Month([Date])) &  "|" & Number.ToText(Number.RoundUp( [Day] / 7, 0)), type text },

            // status/place
            { "CurrentMonth", each [End of Month] = CurrentEOM, type logical }, 
            { "PastMonth", each [Date] < Date.From(Date.StartOfMonth(DateTime.LocalNow())), type logical }, 
            { "CurrentYTD", each ([Fiscal Year]= CurrentFiscalYear) and ([End of Month] <= CurrentEOM), type logical },
            { "CurrentFiscalYear", each [Fiscal Year] = CurrentFiscalYear, type logical },
            
            { "Type", each if [Date] < TodaysDate then "Actuals" else "Forecast", type text }
        }
    ),

    // segment into financial years

    tableFinancialYears = 
        let
            subTables = List.Transform(
                years,
                each 
                    let
                        _fy = _,
                        datesThisFY = Table.SelectRows(
                            addColumns,
                            each [#"Fiscal Year (Week Basis)"] = _fy
                        )    
                    in
                        datesThisFY
            ),
            onlyWholeFYs = List.Select(
                subTables, 
                each Table.RowCount(_) > 50
            ),

            addIndex = List.Transform(
                onlyWholeFYs, 
                each 
                    let
                        addIndex = Table.AddIndexColumn(
                            _, 
                            "Week Number (FY)", 
                            1, 
                            1, 
                            Int8.Type
                        ),

                        weekNumber = Table.TransformColumns(
                            addIndex, 
                            {
                                {
                                    "Week Number (FY)",
                                    each Number.RoundUp(_ / 7),
                                    Int8.Type
                                }
                            }
                        )
                    in
                        weekNumber
            )
        in
            addIndex,
    recombine = Table.Combine(tableFinancialYears)

    
in
    recombine

let
    Source = Csv.Document(
        Text.Trim("
Month Name,Short Name,Calendar Order,Financial Year Order
January,Jan,1,7
February,Feb,2,8
March,Mar,3,9
April,Apr,4,10
May,May,5,11
June,Jun,6,12
July,Jul,7,1
August,Aug,8,2
September,Sep,9,3
October,Oct,10,4
November,Nov,11,5
December,Dec,12,6        
        "), 
        {"Month Name", "Short Name", "Calendar Order", "Financial Year Order"}
    ),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Month Name", type text}, {"Short Name", type text}, {"Calendar Order", Int64.Type}, {"Financial Year Order", Int64.Type}})
in
    #"Changed Type"