Fiscal Calendar

[StephenGW]

All,

I need a way to setup a fiscal calendar table. The fiscal year looks like this.

Starts 12/27/20 and ends 12/25/21 this also uses a 4,5,4 setup where month 1 has 4 weeks, 2 has 5 weeks, 3 has 4 weeks and that pattern repeats.

4, 5, 4

4, 5, 4

I found this thread and it looks close. Solved: Custom Fiscal Year Calendar - Microsoft Power BI Community When using this code it makes a table that I think is right but it rns from 1999 to 2018 and I need it to do this year and beyond. I couldn't figure out how to adjust the dates.

let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
 let
 DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
 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"}}),
 InsertYear = Table.AddColumn(RenamedColumns, "CALYear", each Date.Year([Date])),
 InsertQuarter = Table.AddColumn(InsertYear, "CALQuarterOfYear", each Date.QuarterOfYear([Date])),
 InsertMonth = Table.AddColumn(InsertQuarter, "CALMonthOfYear", each Date.Month([Date])),
 InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
 InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [CALYear] * 10000 + [CALMonthOfYear] * 100 + [DayOfMonth]),
 InsertMonthName = Table.AddColumn(InsertDayInt, "CALMonthName", each Date.ToText([Date], "MMMM", Culture), type text),
 InsertMonthShort = Table.AddColumn(InsertMonthName, "CALMonthNameShort", each Date.ToText([Date], "MMM", Culture), type text),
 InsertCalendarMonth = Table.AddColumn(InsertMonthShort, "CALMonthInCalendar", each (try(Text.Range([CALMonthName],0,3)) otherwise [CALMonthName]) & " " & Number.ToText([CALYear])),
 InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "CALQuarterInCalendar", each "Q" & Number.ToText([CALQuarterOfYear]) & " " & Number.ToText([CALYear])),
 InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date],1)+1),
 InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
 InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEndingFriday", each Date.EndOfWeek([Date],6), type date),

 InsertFyYear = Table.AddColumn(InsertWeekEnding, "zFyYearSort", each if [CALMonthOfYear] <= 6 then Date.Year([Date])-1 else Date.Year([Date])),
 InsertFyYearCode  = Table.AddColumn(InsertFyYear , "FyYear", each Text.End(Text.From([zFyYearSort]),2)&"/"&Text.End(Text.From([zFyYearSort]+1),2),type text),
 InsertFyQtr = Table.AddColumn(InsertFyYearCode  , "FyQuarter", each if [CALMonthOfYear] <= 6 then "Q"&Text.From(Date.QuarterOfYear([Date])+2) else "Q"&Text.From(Date.QuarterOfYear([Date])-2)),
 InsertFyMonth = Table.AddColumn(InsertFyQtr , "FyMonth", each if [CALMonthOfYear] <= 6 then Date.Month([Date])+6 else Date.Month([Date])-6),
 InsertFyYearQtr = Table.AddColumn(InsertFyMonth , "FyYearQtr", each [FyYear] & "-" & [FyQuarter], type text),
 InsertFyYearMonth = Table.AddColumn(InsertFyYearQtr , "FyYearMonth", each [FyYear] & "-" & [CALMonthNameShort], type text),
 InsertFyYearMonthNum = Table.AddColumn(InsertFyYearMonth, "FyYearMonthNum", each Text.From([zFyYearSort]) & "-" & Text.PadStart(Text.From([FyMonth]),2,"0"), type text),

 InsertCurrentThursday = Table.AddColumn(InsertFyYearMonthNum , "CurrentThursday", each Date.AddDays([Date], -Date.DayOfWeek([Date],1) + 3), type date),
 InsertISOWeekJan4 = Table.AddColumn(InsertCurrentThursday, "ISOWeekJan4", each Date.FromText(Number.ToText(Date.Year([CurrentThursday])) & "-01-04") ,type date),
 InsertISOWeekYear = Table.AddColumn(InsertISOWeekJan4, "ISOYear", each Date.Year([CurrentThursday])) ,
 InsertISOWeekFirstMon = Table.AddColumn(InsertISOWeekYear, "ISOWeekFirstMon", each
 if [CurrentThursday] < [ISOWeekJan4]
 then Date.AddDays([CurrentThursday],-3)
 else Date.AddDays([ISOWeekJan4], - Date.DayOfWeek([ISOWeekJan4],1) )
 ,type date),
 InsertISOWeekNum = Table.AddColumn(InsertISOWeekFirstMon, "ISOWeek", each Number.RoundUp(((Duration.Days(Duration.From([Date] - [ISOWeekFirstMon]))+1) /7 )), type number),
 InsertISOWeekID = Table.AddColumn(InsertISOWeekNum, "ISOWeekID", each [ISOYear] * 100 + [ISOWeek], type number),
 InsertISOWeekName = Table.AddColumn(InsertISOWeekID, "ISOWeekName", each Text.From([ISOYear]) & "W" & Text.End( "0" & Text.From(([ISOWeek]*10) + [DayInWeek]),3)),
 InsertISOWeekNameLong = Table.AddColumn(InsertISOWeekName, "ISOWeekNameLong", each Text.From([ISOYear]) & "-W" & Text.End( "0" & Text.From([ISOWeek]),2) & "-" & Text.From([DayInWeek])),

 fnPeriod445a = (weekNum) => let
 Periods =
 {
 {(x)=>x<5, [P=1,Q=1,M="Jan"]},
 {(x)=>x<9, [P=2,Q=1,M="Feb"]},
 {(x)=>x<14, [P=3,Q=1,M="Mar"]},
 {(x)=>x<18, [P=4,Q=2,M="Apr"]},
 {(x)=>x<22, [P=5,Q=2,M="May"]},
 {(x)=>x<27, [P=6,Q=2,M="Jun"]},
 {(x)=>x<31, [P=7,Q=3,M="Jul"]},
 {(x)=>x<35, [P=8,Q=3,M="Aug"]},
 {(x)=>x<40, [P=9,Q=3,M="Sep"]},
 {(x)=>x<44, [P=10,Q=4,M="Oct"]},
 {(x)=>x<48, [P=11,Q=4,M="Nov"]},
 {(x)=>true, [P=12,Q=4,M="Dec"]}
 },
 Result = List.First(List.Select(Periods, each _{0}(weekNum))){1}
 in
 Result,

 InsertPeriod445 = Table.AddColumn(InsertISOWeekNameLong, "Period445Record", each fnPeriod445a([ISOWeek])),
 ExpandPeriod445 = Table.ExpandRecordColumn(InsertPeriod445, "Period445Record", {"P","Q","M" }, {"ISOMonth", "ISOQuarter", "ISOMonthName"}),
 RemovedColumns = Table.RemoveColumns(ExpandPeriod445,{"CurrentThursday", "ISOWeekFirstMon", "ISOWeekJan4"}),
 DaysFromToday = Table.AddColumn(RemovedColumns, "Days from Today" , each Date.From(DateTime.LocalNow()) - [Date]),
 WeeksFromToday = Table.AddColumn(DaysFromToday, "Weeks from Today" , each (Date.From(DateTime.LocalNow()) - [Date])/7),
 MonthsFromToday = Table.AddColumn(WeeksFromToday, "Months from Today" , each (Date.From(DateTime.LocalNow()) - [Date])/(365/12)),
 ChangedType1 = Table.TransformColumnTypes(MonthsFromToday,{{"Date", type date}, {"CALYear", Int64.Type}, {"CALQuarterOfYear", Int64.Type}, {"Days from Today", Int64.Type}, {"Weeks from Today", Int64.Type}, {"Months from Today", Int64.Type}, {"CALMonthOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DateInt", Int64.Type}, {"CALMonthName", type text}, {"CALMonthNameShort", type text}, {"CALMonthInCalendar", type date}, {"CALQuarterInCalendar", type text}, {"DayInWeek", Int64.Type}, {"DayOfWeekName", type text}, {"WeekEndingFriday", type date}, {"zFyYearSort", Int64.Type},{"FyYear", type text}, {"FyQuarter", type text}, {"FyMonth", Int64.Type}, {"ISOYear", Int64.Type}, {"ISOWeek", Int64.Type}, {"ISOWeekID", Int64.Type}, {"ISOWeekName", type text}, {"ISOWeekNameLong", type text}, {"ISOMonth", Int64.Type}, {"ISOQuarter", Int64.Type}, {"ISOMonthName", type text}}),
 InFuture = Table.AddColumn(ChangedType1 , "Date in Future" , each if Number.Sign([Days from Today]) = -1 then true else false, type logical),
 ReorderedColumns = Table.ReorderColumns(InFuture ,{"Date", "CALYear", "CALQuarterOfYear", "CALMonthOfYear", "DayOfMonth", "DateInt", "CALMonthName", "CALMonthNameShort", "CALMonthInCalendar", "CALQuarterInCalendar", "DayInWeek", "DayOfWeekName", "WeekEndingFriday", "zFyYearSort", "FyYear", "FyQuarter", "FyMonth", "FyYearQtr", "FyYearMonth", "FyYearMonthNum", "ISOYear", "ISOMonth", "ISOQuarter", "ISOWeek", "ISOMonthName", "ISOWeekID", "ISOWeekName", "ISOWeekNameLong"})

 in
 ReorderedColumns ,
    #"Invoked FunctionCreateDateTable" = CreateDateTable(#date(1999, 01, 01), #date(2018, 12, 31), "en-US")
in
    #"Invoked FunctionCreateDateTable" 

 Maybe there is an all around eaiser way to set the start of a fiscal year and a custom 4, 5, 4 week setup but I am struggling.

Any help?

Thanks

[Anonymous]

I am looking for standard 4-5-4 calendar where start date is jan-6-2019 and end dare is dec-30-2023 and week 1 starts on 1st sunday of every year ....if you have similar requirement, Can you please share the pbix or code if possible?

[mahoneypat · Accepted solution]

Please see this article/video.  You should be able to easily adapt the provided M code for your custom calendar.

445 Calendar with 53-Week Years – Hoosier BI

Pat

[StephenGW]

@mahoneypat 

I have used this solution for my calendar but there is one problem. I need the week number and this only has the FW_Index which does not reset at the beginning of the fiscal year. Is there any way to add a Week Number column that resets at the beginning of each fiscal year?

Thanks

[mahoneypat]

You should be able to adapt this expression to meet your needs in a custom column.

let

FYstartmonth = 10,
refdate = if Date.Month([Date])>=FYstartmonth then #date(Date.Year([Date]), FYstartmonth, 1) else #date(Date.Year([Date]) - 1, FYstartmonth, 1)
in
Number.RoundUp(1+ Duration.TotalDays([Date] - refdate)/7,0)

Pat

[StephenGW]

@mahoneypat 

It's still a little off. I think maybe because the start and end of the months is not always on the first or last day of the month. 

This screenshot shows it is starting at week 2 when this is the first 3 days of the fiscal year for me and should be the start of week 1. 

Then you can see in this one it should start over with week 1 on 1/2/22 but it is starting with week 1 on 1/1/22 and then week 2 on 1/2/22. 

All I could see to adapt the code was to change the FYstartmonth = 10 to FYstartmonth = 1?

Any help would be great!

Thanks,

[StephenGW]

@mahoneypat 

Perfect! I searched and searched for this and went through many threads here. I was uanble to get one to work but this one was easy and works great!

Thank you!

[Anonymous]

I am looking for standard 4-5-4 calendar where start date is jan-6-2019 and end dare is dec-30-2023 and week 1 starts on 1st sunday of every year ....if you have similar requirement, Can you please share the pbix or code if possible?