Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
StephenGW
Helper II
Helper II

Fiscal Calendar

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

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@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

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@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. 

StephenGW_0-1641994693129.png

 

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. 

StephenGW_1-1641994809785.png

 

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

 

Any help would be great!

 

Thanks,

 

@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
Not applicable

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?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.