I want to create a custom date table with financial years, the new financial year begins October 1st and there are 4 quarters which run like so
Oct =H1 (Quarter 1), FY 24
Nov =H1 (Quarter 1), FY 24
Dec =H1 (Quarter 1), FY 24
Jan =H2 (Quarter 2), FY 24
Feb =H2 (Quarter 2), FY 24
Mar =H2 (Quarter 2), FY 24
Apr =H3 (Quarter 3), FY 24
May=H3 (Quarter 3), FY 24
Jun=H3 (Quarter 3), FY 24
Jul=H4 (Quarter 4), FY 24
Aug=H4 (Quarter 4), FY 24
Sep=H4 (Quarter 4), FY 24
The start date is 2020, 01, 01, and should follow through till end of FY 24 (30 September 2024)
Can someone provide a m query to faciliate this, I was using following but it keeps saying syntax error.
let
DayCount = Duration.Days(Duration.From(EndDate = #date(2024,30,09) - startDate = #date(2020, 1, 1),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertDayName = Table.AddColumn(RenamedColumns, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
InsertDayWeek = Table.AddColumn(InsertDayName, "DayInWeek", each Date.DayOfWeek([Date],6)+1),
InsertWeekEnding = Table.AddColumn(InsertDayWeek, "WeekEndingFriday", each Date.EndOfWeek([Date],6), type date),
InsertCurrentSaturday = Table.AddColumn(InsertWeekEnding, "CurrentSaturday", each Date.AddDays([Date], -Date.DayOfWeek([Date],6)), type date),
DateOffset = Table.AddColumn(InsertCurrentSaturday, "Offset", each Date.FromText(Number.ToText(Date.Year([CurrentSaturday])) & "-02-01") - [CurrentSaturday]),
#"Changed Type" = Table.TransformColumnTypes(DateOffset,{{"Offset", Int64.Type}}),
InsertISOWeekFeb1 = Table.AddColumn(#"Changed Type", "ISOWeekFeb1", each if [Offset] > 6 then Date.FromText(Number.ToText(Date.Year([CurrentSaturday])-1) & "-02-01") else Date.FromText(Number.ToText(Date.Year([CurrentSaturday])) & "-02-01"),type date),
InsertISOWeekYear = Table.AddColumn(InsertISOWeekFeb1, "ISOWeekYear", each Date.Year([ISOWeekFeb1])),
InsertISOWeekFirstSat = Table.AddColumn(InsertISOWeekYear, "ISOWeekFirstSat", each if [CurrentSaturday] < [ISOWeekFeb1]
then Date.AddDays([CurrentSaturday],0)
else Date.AddDays([ISOWeekFeb1], - Date.DayOfWeek([ISOWeekFeb1],6) ), type date),
InsertFYWeekNum = Table.AddColumn(InsertISOWeekFirstSat, "ISOWeekNum", each Number.RoundUp(((Duration.Days(Duration.From([Date] - [ISOWeekFirstSat]))+1) /7 )), type number),
FiscalYear = Table.AddColumn(InsertFYWeekNum, "FY", each [ISOWeekYear]+1),
InsertFYWeekID = Table.AddColumn(FiscalYear, "ISOWeekID", each [FY] * 100 + [ISOWeekNum], type number),
InsertIFYWeekNameLong = Table.AddColumn(InsertFYWeekID, "ISOWeekNameLong", each Text.From([FY]) & "-W" & Text.End( "0" & Text.From([ISOWeekNum]),2) & "-" & Date.ToText([Date],"ddd")),
#"Renamed Columns" = Table.RenameColumns(InsertIFYWeekNameLong,{{"ISOWeekNameLong", "FYWeekNameLong"}, {"ISOWeekID", "FYWeekID"}, {"ISOWeekNum", "FYWeekNum"}, {"ISOWeekFirstSat", "FYWeekFirstSat"}}),
#"FY Quarter" = Table.AddColumn(#"Renamed Columns", "FY Quarter", each if [FYWeekNum] <= 13 then 1 else if [FYWeekNum] >= 14 and [FYWeekNum] <= 26 then 2 else if [FYWeekNum] >= 27 and [FYWeekNum] <= 39 then 3 else 4),
#"Week of FY Quarter" = Table.AddColumn(#"FY Quarter", "Week of Quarter", each if [FYWeekNum] <> 53 then ([FYWeekNum] - (Number.RoundUp([FYWeekNum]/13)-1) * 13) else 14),
#"Quarter Week ID" = Table.AddColumn(#"Week of FY Quarter", "QtrWeekID", each [FY Quarter]*100+[Week of Quarter]),
#"FY Quarter ID" = Table.AddColumn(#"Quarter Week ID", "FYQtrID", each [FY]*100+[FY Quarter]),
#"Changed Type1" = Table.TransformColumnTypes(#"FY Quarter ID",{{"FY Quarter", Int64.Type}, {"Week of Quarter", Int64.Type}, {"QtrWeekID", Int64.Type}, {"FYQtrID", Int64.Type}}),
fnPeriod454a = (weekNum) => let
Periods =
{
{(x)=>x<5, [P=2, M="Jan"]},
{(x)=>x<10, [P=2, M="Feb"]},
{(x)=>x<14, [P=3, M="Mar"]},
{(x)=>x<18, [P=3, M="Apr"]},
{(x)=>x<23, [P=3, M="Jun"]},
{(x)=>x<27, [P=4, M="Jul"]},
{(x)=>x<31, [P=4, M="Aug"]},
{(x)=>x<36, [P=4, M="Sep"]},
{(x)=>x<40, [P=1, M="Oct"]},
{(x)=>x<44, [P=1, M="Nov"]},
{(x)=>x<49, [P=1, M="Dec"]},
{(x)=>true, [P=2, M="Jan"]}
},
Result = List.First(List.Select(Periods, each _{0}(weekNum))){1}
in
Result,
InsertPeriod454 = Table.AddColumn(#"Changed Type1", "Period454Record", each fnPeriod454a([FYWeekNum])),
#"Expanded Period454Record" = Table.ExpandRecordColumn(InsertPeriod454, "Period454Record", {"M", "P"}, {"M", "P"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Period454Record",{{"M", "FY Month Name"}, {"P", "FY Month ID"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"CurrentSaturday", "Offset", "ISOWeekFeb1", "ISOWeekYear", "FYWeekFirstSat"})
in
#"Removed Columns"
