Forum Discussion
Fiscal Calendar
HI, I need some help to create a Financial Calander ;
I need 13 Periods for each year. Most have 4 weeks and for 2 of my years the 13th Period has 5 weeks; these are the year ranges:
I modified the code from this article to create your needed Date table. The min/max dates seem to match what you listed.
445 Calendar with 53-Week Years – Hoosier BI
let // Enter the date in Advanced Editor for the first day of the earliest Fiscal Year in #date(yyyy,m,d) format StartDate = #date(2015, 9, 13), // Enter the desired range of years as List in Advanced Editor YearRange = {2016 .. 2021}, YearsWith53Weeks = {2015, 2020}, StartingTable = Table.FromColumns({YearRange}, {"Year"}), AddNumberOfWeeksColumn = Table.AddColumn( StartingTable, "Weeks", each if List.Contains(YearsWith53Weeks, [Year]) then 53 else 52 ), #"Changed Type4" = Table.TransformColumnTypes( AddNumberOfWeeksColumn, {{"Year", Int64.Type}, {"Weeks", Int64.Type}} ), // In the Advanced Editor, enter the two patterns for 52 and 53 week years as a list of weeks per fiscal month AddListOfMonthAndWeekCounts = Table.AddColumn( #"Changed Type4", "Custom", each if [Weeks] = 53 then List.Zip({{1 .. 13}, {4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 5}}) else List.Zip({{1 .. 13}, {4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4}}) ), #"Expanded Custom" = Table.ExpandListColumn(AddListOfMonthAndWeekCounts, "Custom"), #"Extracted Values" = Table.TransformColumns( #"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text} ), #"Split Column by Delimiter" = Table.SplitColumn( #"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"FM", "NumWeeks"} ), #"Changed Type" = Table.TransformColumnTypes( #"Split Column by Delimiter", {{"FM", Int64.Type}, {"NumWeeks", Int64.Type}} ), AddListOfWeeksColumn = Table.AddColumn(#"Changed Type", "WeekInFM", each {1 .. [NumWeeks]}), #"Expanded Custom1" = Table.ExpandListColumn(AddListOfWeeksColumn, "WeekInFM"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom1", {{"WeekInFM", Int64.Type}}), AddWeekIndex = Table.AddIndexColumn(#"Changed Type1", "FW_Index", 1, 1, Int64.Type), AddFWinYear = Table.AddColumn( AddWeekIndex, "FWinYear", each let thisyear = [Year], thisindex = [FW_Index], result = Table.RowCount( Table.SelectRows(AddWeekIndex, each [Year] = thisyear and [FW_Index] <= thisindex) ) in result ), Add7DayListPerWeek = Table.AddColumn(AddFWinYear, "WeekDay", each {1 .. 7}), #"Expanded WeekDay" = Table.ExpandListColumn(Add7DayListPerWeek, "WeekDay"), #"Changed Type2" = Table.TransformColumnTypes(#"Expanded WeekDay", {{"WeekDay", Int64.Type}}), RemoveUnneededColumns = Table.RemoveColumns(#"Changed Type2", {"Weeks", "NumWeeks", "WeekDay"}), AddDayIndex = Table.AddIndexColumn(RemoveUnneededColumns, "DayIndex", 0, 1, Int64.Type), AddDatesBasedOnStartDateAndDayIndex = Table.AddColumn( AddDayIndex, "Date", each Date.AddDays(StartDate, [DayIndex]), type date ) in AddDatesBasedOnStartDateAndDayIndexPat
6 Replies
- Greg_DecklerCommunity Champion
keekee Here is a Power Query solution: 13 Period Fiscal Calendar (Power Query M function) - M Code Showcase - Enterprise DNA Forum
If that doesn't work for you I can try to find something or create something with DAX.
- keekeeFrequent Visitor
HI, many thanks for your response, I keep getting the error:
This visual contains one or more filters with deleted columns, type mismatches, or other breaking modelling changes
- keekeeFrequent Visitor
DAX would be great; I do have a calander that I inherited from a previous employee but it is totally based on the 544 454 455 structure;
thanks again
- keekeeFrequent Visitor
HI, thanks so much for your help - I did get the Query working in the end and its nearly there but I am missing my 53 rd week where I need it;
my time periods are as below
e.g. 2017 I need to start the year on 17th September not the 10th and finish on the 14th September.
Many thanks again
{ 2016, "2016-09-18", "2017-09-16" },{ 2017, "2017-09-17", "2018-09-15" },{ 2018, "2018-09-16", "2019-09-14" },{ 2019, "2019-09-15", "2020-09-12" },{ 2020, "2020-09-13", "2021-09-18" },{ 2021, "2021-09-19", "2022-09-17" }- ppm1Solution Sage
I modified the code from this article to create your needed Date table. The min/max dates seem to match what you listed.
445 Calendar with 53-Week Years – Hoosier BI
let // Enter the date in Advanced Editor for the first day of the earliest Fiscal Year in #date(yyyy,m,d) format StartDate = #date(2015, 9, 13), // Enter the desired range of years as List in Advanced Editor YearRange = {2016 .. 2021}, YearsWith53Weeks = {2015, 2020}, StartingTable = Table.FromColumns({YearRange}, {"Year"}), AddNumberOfWeeksColumn = Table.AddColumn( StartingTable, "Weeks", each if List.Contains(YearsWith53Weeks, [Year]) then 53 else 52 ), #"Changed Type4" = Table.TransformColumnTypes( AddNumberOfWeeksColumn, {{"Year", Int64.Type}, {"Weeks", Int64.Type}} ), // In the Advanced Editor, enter the two patterns for 52 and 53 week years as a list of weeks per fiscal month AddListOfMonthAndWeekCounts = Table.AddColumn( #"Changed Type4", "Custom", each if [Weeks] = 53 then List.Zip({{1 .. 13}, {4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 5}}) else List.Zip({{1 .. 13}, {4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4}}) ), #"Expanded Custom" = Table.ExpandListColumn(AddListOfMonthAndWeekCounts, "Custom"), #"Extracted Values" = Table.TransformColumns( #"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text} ), #"Split Column by Delimiter" = Table.SplitColumn( #"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"FM", "NumWeeks"} ), #"Changed Type" = Table.TransformColumnTypes( #"Split Column by Delimiter", {{"FM", Int64.Type}, {"NumWeeks", Int64.Type}} ), AddListOfWeeksColumn = Table.AddColumn(#"Changed Type", "WeekInFM", each {1 .. [NumWeeks]}), #"Expanded Custom1" = Table.ExpandListColumn(AddListOfWeeksColumn, "WeekInFM"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom1", {{"WeekInFM", Int64.Type}}), AddWeekIndex = Table.AddIndexColumn(#"Changed Type1", "FW_Index", 1, 1, Int64.Type), AddFWinYear = Table.AddColumn( AddWeekIndex, "FWinYear", each let thisyear = [Year], thisindex = [FW_Index], result = Table.RowCount( Table.SelectRows(AddWeekIndex, each [Year] = thisyear and [FW_Index] <= thisindex) ) in result ), Add7DayListPerWeek = Table.AddColumn(AddFWinYear, "WeekDay", each {1 .. 7}), #"Expanded WeekDay" = Table.ExpandListColumn(Add7DayListPerWeek, "WeekDay"), #"Changed Type2" = Table.TransformColumnTypes(#"Expanded WeekDay", {{"WeekDay", Int64.Type}}), RemoveUnneededColumns = Table.RemoveColumns(#"Changed Type2", {"Weeks", "NumWeeks", "WeekDay"}), AddDayIndex = Table.AddIndexColumn(RemoveUnneededColumns, "DayIndex", 0, 1, Int64.Type), AddDatesBasedOnStartDateAndDayIndex = Table.AddColumn( AddDayIndex, "Date", each Date.AddDays(StartDate, [DayIndex]), type date ) in AddDatesBasedOnStartDateAndDayIndexPat
- keekeeFrequent Visitor
thanks so much; have a great day