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"]}
 },