Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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?
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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,
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!
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?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
103 | |
98 | |
90 | |
70 |
User | Count |
---|---|
166 | |
131 | |
129 | |
102 | |
98 |