Forum Discussion

dkay84_PowerBI's avatar
dkay84_PowerBI
Microsoft Employee
9 years ago
Solved

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.

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    9 years ago

    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"

29 Replies

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Microsoft Employee

    So far I have (using some of my own logic as well as piecing together steps from http://www.powerpivotpro.com/2015/03/create-a-445-calender-using-power-query/) created a custom FY calendar that accurately tracks the dynamic situation given above.  However, where I run into a problem is with the 53rd week that occurs every 6 years.  This 53rd week is added on to the end, so the last quarter is 4-5-5 in length.

     

    The 53 week years in my Dates table are FY 2014 and 2020.  When it gets to the end of week 52 of FY 2014 (1/18/2014 - 1/24/2014) the custom columns reset at 1, but I need them to address the 53rd week issue and push all later dates columns (so week 2 of FY 2015 becomes week 1).

     

    If someone could tell me if there is a way to attach a file to a post I will upload my PBIX.

  • Chris99's avatar
    Chris99
    Advocate III

    Were you aware that the latest Feb 2018 update (2.55.5010.521) allows a custom date table to be specified? It may be worth looking at this.

     

    Chris

    • AmberM's avatar
      AmberM
      Helper II

      Thank you Chris, and yes, I did see this. My data is only listed at a fiscal week level, so the dynamic SAMEPERIODLASTYEAR calculations do not add up correctly. I'm new to Power BI, so it may be figuring out how to create with an OFFSET calc.

      • Chris99's avatar
        Chris99
        Advocate III

        I think you would need to hold fiscal week, month, quarter and year information in your date table.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi, 

     

    I know this is an old post, but hopefully you will see this:

    I have a fiscal date table setup in my PowerBI. I'm trying to do Y/Y calculations with the SAMEPERIODLASTYEAR function. The function itself works, but, given that our fiscal year doesn't start on the exact same day every year, I'm running into an issue.

    I.e., FY21-Q1 began on February 1st, 2020 whereas FY22-Q1 began on January 30th, 2021. For my Y/Y calculations, PowerBI obviously doesn't consider this... So, PowerBI compares January 30th, 2021 to January 30th, 2020 rather than February 1st, 2020. Instead of comparing exact dates to exact dates, I want to compare Day 1 of Week 1 of Quarter 1 of FY21 to Day 1 of Week 1 of Quarter 1 of FY20... Does this make sense?

    Any ideas?