Please can someone help; I've created a custom date table in the advanced query editor to create a custom 13 period calendar. However from 01/04/2020 instead of reverting back to period 1,2,3 it goes up to period 14,15,16 etc. what do I need to do to correct this? Ideally it would look like 19/20.01 ; 19/20.02 ; 19/20.03 etc. and then 20/21.01 ; 20/21.02 ; 20/21.03 etc.
The Advanced editor looks like this currently:
let
Calendar = #table(
{"PeriodStart", "PeriodEnd", "FiscalYear" },
{ { #date ( 2019, 04, 01 ), #date ( 2019, 04, 27 ), "19/20" },
{ #date ( 2019, 04, 28 ), #date ( 2019, 5, 25 ), "19/20" },
{ #date ( 2019, 5, 26 ), #date ( 2019, 6, 22 ), "19/20" },
{ #date ( 2019, 6, 23 ), #date ( 2019, 7, 20 ), "19/20" },
{ #date ( 2019, 7, 21 ), #date ( 2019, 8, 17 ), "19/20" },
{ #date ( 2019, 8, 18 ), #date ( 2019, 9, 14 ), "19/20" },
{ #date ( 2019, 9, 15 ), #date ( 2019, 10, 12 ), "19/20" },
{ #date ( 2019, 10, 13 ), #date ( 2019, 11, 09 ), "19/20" },
{ #date ( 2019, 11, 10 ), #date ( 2019, 12, 07 ), "19/20" },
{ #date ( 2019, 12, 8 ), #date ( 2020, 1, 4 ), "19/20" },
{ #date ( 2020, 1, 5 ), #date ( 2020, 2, 1 ), "19/20" },
{ #date ( 2020, 2, 2 ), #date ( 2020, 3, 1 ), "19/20" },
{ #date ( 2020, 3, 2 ), #date ( 2020,3, 31 ), "19/20" },
{ #date ( 2020, 4, 1 ), #date ( 2020,5, 2 ), "20/21" },
{ #date ( 2020, 5, 3 ), #date ( 2020,5, 30 ), "20/21" },
{ #date ( 2020, 5, 31 ), #date ( 2020,6, 27 ), "20/21" },
{ #date ( 2020, 6, 28 ), #date ( 2020,7, 25 ), "20/21" },
{ #date ( 2020, 7, 26 ), #date ( 2020,8, 22 ), "20/21" },
{ #date ( 2020, 8, 23 ), #date ( 2020,9, 19 ), "20/21" },
{ #date ( 2020, 9, 20 ), #date ( 2020,10, 17 ), "20/21" },
{ #date ( 2020, 10, 18 ), #date ( 2020,11, 14 ), "20/21" },
{ #date ( 2020, 11, 15 ), #date ( 2020,12, 12 ), "20/21" },
{ #date ( 2020, 12, 13 ), #date ( 2021,1, 09 ), "20/21" },
{ #date ( 2021, 1, 10 ), #date ( 2021,2, 06 ), "20/21" },
{ #date ( 2021, 2, 07 ), #date ( 2021,3, 06 ), "20/21" },
{ #date ( 2021, 3, 07 ), #date ( 2021,3, 31 ), "20/21" },
{ #date ( 2021, 4, 1 ), #date ( 2021,5, 1 ), "21/22" },
{ #date ( 2021, 5, 2 ), #date ( 2021,5, 29 ), "21/22" },
{ #date ( 2021, 5, 30 ), #date ( 2021,6, 26 ), "21/22" },
{ #date ( 2021, 6, 27 ), #date ( 2021,7, 24 ), "21/22" },
{ #date ( 2021, 7, 25 ), #date ( 2021,8, 21 ), "21/22" },
{ #date ( 2021, 8, 22 ), #date ( 2021,9, 18 ), "21/22" },
{ #date ( 2021, 9, 19 ), #date ( 2021,10, 16 ), "21/22" },
{ #date ( 2021, 10, 17 ), #date ( 2021,11, 13 ), "21/22" },
{ #date ( 2021, 11, 14 ), #date ( 2021,12, 11 ), "21/22" },
{ #date ( 2021, 12, 12 ), #date ( 2022,1, 08 ), "21/22" },
{ #date ( 2022, 1, 09 ), #date ( 2022,2, 05 ), "21/22" },
{ #date ( 2022, 2, 06 ), #date ( 2022,3, 05 ), "21/22" },
{ #date ( 2022, 3, 06 ), #date ( 2022,3, 31 ), "21/22" },
{ #date ( 2022, 4, 01 ), #date ( 2022,4, 30 ), "22/23" },
{ #date ( 2022, 5, 01 ), #date ( 2022,5, 28 ), "22/23" },
{ #date ( 2022, 5, 29 ), #date ( 2022,06, 25 ), "22/23" },
{ #date ( 2022, 6, 26 ), #date ( 2022,07, 23 ), "22/23" },
{ #date ( 2022, 7, 24 ), #date ( 2022,08, 20 ), "22/23" },
{ #date ( 2022, 8, 21 ), #date ( 2022,09, 17 ), "22/23" },
{ #date ( 2022, 9, 18 ), #date ( 2022,10, 15 ), "22/23" },
{ #date ( 2022, 10, 16 ), #date ( 2022,11, 12 ), "22/23" },
{ #date ( 2022, 11, 13 ), #date ( 2022,12, 10 ), "22/23" },
{ #date ( 2022, 12, 11 ), #date ( 2023,1, 07 ), "22/23" },
{ #date ( 2023, 1, 08 ), #date ( 2023,2, 04 ), "22/23" },
{ #date ( 2023, 2, 05 ), #date ( 2023,3, 04 ), "22/23" },
{ #date ( 2023, 3, 05 ), #date ( 2023,3, 31 ), "22/23" },
{ #date ( 2023, 4, 1 ), #date ( 2023,4, 29 ), "23/24" },
{ #date ( 2023, 4, 30 ), #date ( 2023,5, 27 ), "23/24" },
{ #date ( 2023, 5, 28 ), #date ( 2023,6, 24 ), "23/24" },
{ #date ( 2023, 6, 25 ), #date ( 2023,7, 22 ), "23/24" },
{ #date ( 2023, 7, 23 ), #date ( 2023,8, 19 ), "23/24" },
{ #date ( 2023, 8, 20 ), #date ( 2023,9, 16 ), "23/24" },
{ #date ( 2023, 9, 17 ), #date ( 2023,10, 14 ), "23/24" },
{ #date ( 2023, 10, 15 ), #date ( 2023,11, 11 ), "23/24" },
{ #date ( 2023, 11, 12 ), #date ( 2023,12, 09 ), "23/24" },
{ #date ( 2023, 12, 10 ), #date ( 2024,1, 06 ), "23/24" },
{ #date ( 2024, 1, 07 ), #date ( 2024,2, 03 ),"23/24" },
{ #date ( 2024, 2, 04 ), #date ( 2024,3, 02 ), "23/24" },
{ #date ( 2024, 3, 03 ), #date ( 2024,3, 31 ), "23/24" }
}
),
#"Added PeriodIndex" = Table.AddIndexColumn(Calendar, "PeriodIndex", 1, 1),
#"Added DatesBetween" = Table.AddColumn ( #"Added PeriodIndex", "Date", each List.Transform ( { Number.From ( [PeriodStart] ) ..Number.From ( [PeriodEnd] ) }, each Date.From ( _ ) ) ),
#"Expanded DatesBetween" = Table.ExpandListColumn ( #"Added DatesBetween", "Date" ),
#"Changed Type To Date" = Table.TransformColumnTypes(#"Expanded DatesBetween",{{"Date", type date}}),
#"Added Fiscal Period" = Table.AddColumn(#"Changed Type To Date", "Fiscal Period", each if Number.Mod ( [PeriodIndex], 13 ) = 0 then 13 else Number.Mod([PeriodIndex], 13 ), Int64.Type),
InsertYear = Table.AddColumn(#"Added Fiscal Period", "Year", each Date.Year([Date]), type number),
InsertQuarter = Table.AddColumn(InsertYear, "Quarter Num", each Date.QuarterOfYear([Date]), type number),
InsertCalendarQtr = Table.AddColumn(InsertQuarter, "Quarter Year", each "Q" & Number.ToText([Quarter Num]) & " " & Number.ToText([Year]),type text),
InsertCalendarQtrOrder = Table.AddColumn(InsertCalendarQtr, "Quarter Year Order", each [Year] * 10 + [Quarter Num], type number),
InsertMonth = Table.AddColumn(InsertCalendarQtrOrder, "Month Num", each Date.Month([Date]), type number),
InsertMonthName = Table.AddColumn(InsertMonth, "Month Name", each Date.ToText([Date], "MMMM"), type text),
InsertMonthNameShort = Table.AddColumn(InsertMonthName, "Month Name Short", each Date.ToText([Date], "MMM"), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthNameShort, "Month Year", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year]), type text),
InsertCalendarMonthOrder = Table.AddColumn(InsertCalendarMonth, "Month Year Order", each [Year] * 100 + [Month Num], type number),
InsertWeek = Table.AddColumn(InsertCalendarMonthOrder, "Week Num", each Date.WeekOfYear([Date]), type number),
InsertCalendarWk = Table.AddColumn(InsertWeek, "Week Year", each "W" & Number.ToText([Week Num]) & " " & Number.ToText([Year]), type text),
InsertCalendarWkOrder = Table.AddColumn(InsertCalendarWk, "Week Year Order", each [Year] * 100 + [Week Num], type number),
InsertWeekEnding = Table.AddColumn(InsertCalendarWkOrder, "Week Ending", each Date.EndOfWeek([Date]), type date),
InsertDay = Table.AddColumn(InsertWeekEnding, "Month Day Num", each Date.Day([Date]), type number),
InsertDayInt = Table.AddColumn(InsertDay, "Date Int", each [Year] * 10000 + [Month Num] * 100 + [Month Day Num], type number),
InsertDayWeek = Table.AddColumn(InsertDayInt, "Day Num Week", each Date.DayOfWeek([Date]) + 1, type number),
InsertDayName = Table.AddColumn(InsertDayWeek, "Day Name", each Date.ToText([Date], "dddd"), type text),
InsertWeekend = Table.AddColumn(InsertDayName, "Weekend", each if [Day Num Week] = 1 then "Y" else if [Day Num Week] = 7 then "Y" else "N", type text),
InsertDayNameShort = Table.AddColumn(InsertWeekend, "Day Name Short", each Date.ToText([Date], "ddd"), type text),
InsertIndex = Table.AddIndexColumn(InsertDayNameShort, "Index", 1, 1),
InsertDayOfYear = Table.AddColumn(InsertIndex, "Day of Year", each Date.DayOfYear([Date]), type number),
InsertCurrentDay = Table.AddColumn(InsertDayOfYear, "Current Day?", each Date.IsInCurrentDay([Date]), type logical),
InsertCurrentWeek = Table.AddColumn(InsertCurrentDay, "Current Week?", each Date.IsInCurrentWeek([Date]), type logical),
InsertCurrentMonth = Table.AddColumn(InsertCurrentWeek, "Current Month?", each Date.IsInCurrentMonth([Date]), type logical),
InsertCurrentQuarter = Table.AddColumn(InsertCurrentMonth, "Current Quarter?", each Date.IsInCurrentQuarter([Date]), type logical),
InsertCurrentYear = Table.AddColumn(InsertCurrentQuarter, "Current Year?", each Date.IsInCurrentYear([Date]), type logical),
InsertCompletedDay = Table.AddColumn(InsertCurrentYear, "Completed Days", each if DateTime.Date(DateTime.LocalNow()) > [Date] then "Y" else "N", type text),
InsertCompletedWeek = Table.AddColumn(InsertCompletedDay, "Completed Weeks", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.WeekOfYear(DateTime.Date(DateTime.LocalNow())) > Date.WeekOfYear([Date])) then "Y" else "N", type text),
InsertCompletedMonth = Table.AddColumn(InsertCompletedWeek, "Completed Months", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.Month(DateTime.Date(DateTime.LocalNow())) > Date.Month([Date])) then "Y" else "N", type text),
InsertCompletedQuarter = Table.AddColumn(InsertCompletedMonth, "Completed Quarters", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.QuarterOfYear(DateTime.Date(DateTime.LocalNow())) > Date.QuarterOfYear([Date])) then "Y" else "N", type text),
InsertCompletedYear = Table.AddColumn(InsertCompletedQuarter, "Completed Years", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else "N", type text),
#"Renamed Columns" = Table.RenameColumns(InsertCompletedYear,{{"PeriodStart", "FiscPeriodStart"}, {"PeriodEnd", "FiscPeriodEnd"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"FiscPeriodEnd", type date}, {"FiscPeriodStart", type date}}),
#"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "FiscalYearPeriod", each Text.Combine({[FiscalYear], Text.From([PeriodIndex], "en-US")}, "-"), type text)
in
#"Inserted Merged Column"
