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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply

Custom Fiscal Year Calendar

This is not for the faint of heart but I'm hoping to crowdsource a solution as I work on it myself.  Here is the description of the calendar I need to model in PowerQuery/Query Editor:

 

The Company's Fiscal Year begins with the week containing February 1. This doesn't mean the February 1 is the beginning of their fiscal year. It means that the week CONTAINING February 1 marks the beginning of the year.

The Company's weeks start on Saturday at 12:01 in the morning and run through Friday at midnight (my data doesn't go to time level, only day, so don't worry about time of day).  So, for the beginning of the fiscal year, we first look for the week containing February 1, then find the preceding Saturday, which will then be the beginning of the fiscal year. 

The Company divides its quarters up by a 4-5-4 method. This means that Quarter 1 of any year consists of February, March and April. No matter what year, this always holds true. So the 4-5-4 rule means that the first month of every quarter has 4 weeks, second month has 5 weeks, and the third quarter has 4 weeks. This then repeats for each successive quarter. I have some experience building out a 4-5-4 calendar in PowerQuery but using a static date as the start of the Fiscal Year, not like this situation.

 

Any help is appreciated.

2 ACCEPTED SOLUTIONS

For anyone who is interested, I was able to adapt the approach in this blog post to solve my calendar needs.  Take a look if you're interested in the solution:

 

Date Dimension PBIX

 

View solution in original post

Here is the code from the Advanced Editor for those who prefer not to download the file (red text refers to paramters but can be replaced with hard coded dates or dynamic retrieval of earliest and latest dates in you Fact 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"}}), 
    InsertDayName = Table.AddColumn(RenamedColumns, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text), 
    InsertDayWeek = Table.AddColumn(InsertDayName, "DayInWeek", each Date.DayOfWeek([Date],6)+1), 
    InsertWeekEnding = Table.AddColumn(InsertDayWeek, "WeekEndingFriday", each Date.EndOfWeek([Date],6), type date),    
    InsertCurrentSaturday = Table.AddColumn(InsertWeekEnding, "CurrentSaturday", each Date.AddDays([Date], -Date.DayOfWeek([Date],6)), type date),
    DateOffset = Table.AddColumn(InsertCurrentSaturday, "Offset", each Date.FromText(Number.ToText(Date.Year([CurrentSaturday])) & "-02-01") - [CurrentSaturday]),
    #"Changed Type" = Table.TransformColumnTypes(DateOffset,{{"Offset", Int64.Type}}),
    InsertISOWeekFeb1 = Table.AddColumn(#"Changed Type", "ISOWeekFeb1", each if [Offset] > 6 then Date.FromText(Number.ToText(Date.Year([CurrentSaturday])-1) & "-02-01") else Date.FromText(Number.ToText(Date.Year([CurrentSaturday])) & "-02-01"),type date),
    InsertISOWeekYear = Table.AddColumn(InsertISOWeekFeb1, "ISOWeekYear", each Date.Year([ISOWeekFeb1])),
    InsertISOWeekFirstSat = Table.AddColumn(InsertISOWeekYear, "ISOWeekFirstSat", each if [CurrentSaturday] < [ISOWeekFeb1] 
        then Date.AddDays([CurrentSaturday],0) 
        else Date.AddDays([ISOWeekFeb1], - Date.DayOfWeek([ISOWeekFeb1],6) ), type date),
    InsertFYWeekNum = Table.AddColumn(InsertISOWeekFirstSat, "ISOWeekNum", each Number.RoundUp(((Duration.Days(Duration.From([Date] - [ISOWeekFirstSat]))+1) /7 )), type number),
    FiscalYear = Table.AddColumn(InsertFYWeekNum, "FY", each [ISOWeekYear]+1),
    InsertFYWeekID = Table.AddColumn(FiscalYear, "ISOWeekID", each [FY] * 100 + [ISOWeekNum], type number), 
    InsertIFYWeekNameLong = Table.AddColumn(InsertFYWeekID, "ISOWeekNameLong", each Text.From([FY]) & "-W" & Text.End( "0" & Text.From([ISOWeekNum]),2) & "-" & Date.ToText([Date],"ddd")),
    #"Renamed Columns" = Table.RenameColumns(InsertIFYWeekNameLong,{{"ISOWeekNameLong", "FYWeekNameLong"}, {"ISOWeekID", "FYWeekID"}, {"ISOWeekNum", "FYWeekNum"}, {"ISOWeekFirstSat", "FYWeekFirstSat"}}),
    #"FY Quarter" = Table.AddColumn(#"Renamed Columns", "FY Quarter", each if [FYWeekNum] <= 13 then 1 else if [FYWeekNum] >= 14 and [FYWeekNum] <= 26 then 2 else if [FYWeekNum] >= 27 and [FYWeekNum] <= 39 then 3 else 4),
    #"Week of FY Quarter" = Table.AddColumn(#"FY Quarter", "Week of Quarter", each if [FYWeekNum] <> 53 then ([FYWeekNum] - (Number.RoundUp([FYWeekNum]/13)-1) * 13) else 14),
    #"Quarter Week ID" = Table.AddColumn(#"Week of FY Quarter", "QtrWeekID", each [FY Quarter]*100+[Week of Quarter]),
    #"FY Quarter ID" = Table.AddColumn(#"Quarter Week ID", "FYQtrID", each [FY]*100+[FY Quarter]),
    #"Changed Type1" = Table.TransformColumnTypes(#"FY Quarter ID",{{"FY Quarter", Int64.Type}, {"Week of Quarter", Int64.Type}, {"QtrWeekID", Int64.Type}, {"FYQtrID", Int64.Type}}),

    fnPeriod454a = (weekNum) => let 
      Periods = 
        { 
            {(x)=>x<5,  [P=1, M="Feb"]}, 
      {(x)=>x<10,  [P=2, M="Mar"]}, 
      {(x)=>x<14, [P=3, M="Apr"]}, 
      {(x)=>x<18, [P=4, M="May"]}, 
      {(x)=>x<23, [P=5, M="Jun"]}, 
      {(x)=>x<27, [P=6, M="Jul"]}, 
      {(x)=>x<31, [P=7, M="Aug"]}, 
      {(x)=>x<36, [P=8, M="Sep"]}, 
      {(x)=>x<40, [P=9, M="Oct"]}, 
            {(x)=>x<44, [P=10, M="Nov"]}, 
            {(x)=>x<49, [P=11, M="Dec"]}, 
            {(x)=>true, [P=12, M="Jan"]} 
        }, 
      Result = List.First(List.Select(Periods, each _{0}(weekNum))){1} 
    in 
      Result,

    InsertPeriod454 = Table.AddColumn(#"Changed Type1", "Period454Record", each fnPeriod454a([FYWeekNum])),
    #"Expanded Period454Record" = Table.ExpandRecordColumn(InsertPeriod454, "Period454Record", {"M", "P"}, {"M", "P"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Period454Record",{{"M", "FY Month Name"}, {"P", "FY Month ID"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"CurrentSaturday", "Offset", "ISOWeekFeb1", "ISOWeekYear", "FYWeekFirstSat"})
in
    #"Removed Columns"

View solution in original post

29 REPLIES 29

Hi fbaird, unfortunately it's been a while since a worked on this and I no longer have the report as I rolled off of that engagement and it stayed behind.  From my memory it is either a date value (i.e. #date(year,month,day) or a date code like 20180301).  I'm in the US so it was definitely the US version if any, but maybe this is can change if you system locale settings are not US?

 

EIther way, you only have a few choices to try out so see what works!

Anonymous
Not applicable

Hi Dkay84,

 

Thank you for coming back to me on this. And yes you are right, I will give it whirl and see how we go

 

Thanks

Does your fiscal calendar table have a column for which week of the fiscal year it is?

Anonymous
Not applicable

Hi Chris,

 

Yes weeks are currently available, but at the time it was setup we couldn't customize it for some reason. So I am looking to replace the entire query.

 

The current advanced query looks like this

 

let
    Source = Query1(#date(2015, 1, 1), #date(2022, 1, 1), null),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type", "MonthName", Splitter.SplitTextByPositions({0, 3}, false), {"MonthName.1", "MonthName.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"MonthName.1", type text}, {"MonthName.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"MonthName.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"MonthName.1", "MonthName"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Month-Year", each [MonthName]&"-"&[Year]),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom", "Year", "Year - Copy"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column",{{"Year - Copy", Int64.Type}, {"QuarterOfYear", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "QTR", each "QTR"&""&[QuarterOfYear]),
    #"Inserted Month" = Table.AddColumn(#"Added Custom1", "Month", each Date.Month([Date]), type number),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Inserted Month", "Year", "Year - Copy.1"),
    #"Split Column by Position1" = Table.SplitColumn(#"Duplicated Column1", "Year - Copy.1", Splitter.SplitTextByPositions({0, 2}, false), {"Year - Copy.1.1", "Year - Copy.1.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Year - Copy.1.1", Int64.Type}, {"Year - Copy.1.2", Int64.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type3",{"Year - Copy.1.1"}),
    #"Duplicated Column2" = Table.DuplicateColumn(#"Removed Columns1", "Month", "Month - Copy"),
    #"Changed Type4" = Table.TransformColumnTypes(#"Duplicated Column2",{{"Year - Copy.1.2", type text}, {"Month - Copy", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type4", "Sort purpose", each [#"Month - Copy"]&""&[#"Year - Copy.1.2"])
in
    #"Added Custom2"

@Anonymous-Kudos for the phrase "US or Normal", that made me smile Smiley LOL

Anonymous
Not applicable

Hi Chris,

 

Glad it was amusing mate!

wow! thanks for this. I don't know why there isn't a less technical solution out there.

 

Trying to repurpose your code so I can have my year start on September 1st....

Please be aware, as I pointed out in my description, this calendar doesn't just have a custom start date, but starts on the Saturday of the week of Feb 1.  There is also a 4-5-4 quarter and 53 week year consideration.  If your calendar doesn't have those constraints (only a start date of 9/1) then it is a much easier solution.

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

 

Best Regards,

Herbert

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.