Custom Fiscal Year Calendar

dkay84_PowerBI

That’s a good news that you’ve got your problem solved. You could mark your last reply as solution to close this thread if convenient. :smileyhappy:

Best Regards,

Herbert

I've been using a similar date dimension to the one referenced about for a while now and recently had a customer who used a FY calendar starting on July 1st.  Since nothing I've seen anywhere else did what I needed, I built the following.  In the middle starting at "InsertFyYear" I've added in logic to build some specific columns for an FY Calendar.  

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"

By the way, the ISO calendar part in this is based on a 4-4-5 calendar, if you wanted a 4-5-4 calendar, make the following changes to the Period lookup near the middle.

 Periods =
 {
 {(x)=>x<5, [P=1,Q=1,M="Jan"]},
 {(x)=>x<10, [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<23, [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<36, [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<49, [P=11,Q=4,M="Nov"]},
 {(x)=>true, [P=12,Q=4,M="Dec"]}
 },

How do you handle the calculations for same period last year if you want to compare for example, Fiscal Week 201701 vs 201601? Same for if you want to look at last 2 weeks, last 4 weeks vs prior year? The SAMEPERIODLASTYEAR calculation doesn't add up correctly.

Thank you for posting this!

Thank you so much! this is exactly what I'm looking for! 

Can I get this PIBX?