Making a 445 Date Table with Power Query

Many organizations, particularly in the retail and manufacturing industries, use a 445 calendar.

If yours is such an organization, then you need to “roll your own” (in terms of DAX for time intelligence) if, for instance, you want to do year-over-year/quarter or year/quarter-to-date calculations. Marco Russo explains the Whys and Hows of this subject beautifully in his blog post, Week-Based Time Intelligence in DAX. Before you can write your custom DAX, however, you need a date table that’s 445-ready.

Not all 445 calendars are the same. The name 445 is derived from the description, or definition, if you will, of a quarter. Each quarter consists of 3 periods, the first two of which are 4 weeks long and the last of which is 5 weeks long, for a total of 13 weeks. While that part is standard, the start date of a given year can vary.

Additionally, every ~5.6 years, an extra week is needed (to make up for all those years with 364-day years (7x13x4). The convention seems to be to tack that extra week onto the end of period 12, making it a 6-week period. My solution is agnostic to this issue, and should work regardless.

Here’s how I built a 445 date table for one of our clients, in two phases.

Phase I – the Base Date Table

The first thing needed to build a DAX-ready 445 date table is the specifics of your organization’s fiscal year(s). In my case, our client provided us with a three-year table in Excel containing a row for each period, and one column each for:

• fiscal year
• period number (1-12)
• period start date
• period end date

Your finance department is a good place to start in your search for such a table. I call this the Periods table.

The next thing needed is a standard date table, with one row for every day of every year of interest. For that I looked to Chris Webb’s excellent post, Generating A Date Dimension Table in Power Query.

Chris’s approach utilizes two queries and a parameter table (which you supply, based on your fiscal requirements). The parameter table is a single-row table containing the first and last dates of your future 445 table. All of Chris’s heavy lifting is in the first query (actually a function, not a query). His second query simply calls this function, using the data from the parameter table to set the bounds of the date table.

I created my own parameter table, which I call Bounds, by extracting the start and end dates from Periods:

let
    Source = Periods,

    // Find the max date in the End Date column
    Find_Max_Date = Table.AddColumn ( Source, "Max Date", each Table.Max( Source, "End Date" ) ),
    Expanded_Max_Date = Table.ExpandRecordColumn( Find_Max_Date, "Max Date", { "End Date"}, { "End Date.1" } ),

    // All we need is one row containing the earliest and latest dates of our overall calendar
    Kept_First_Rows = Table.FirstN ( Expanded_Max_Date, 1 ),
    Removed_Columns = Table.RemoveColumns ( Kept_First_Rows, { "id", "Year", "Period", "End Date" } ),
    Renamed_Columns = Table.RenameColumns(Removed_Columns,{{"End Date.1", "End Date"}})
in
    Renamed_Columns

I ended up making two changes to Chris’s function. Firstly, I renamed the Year column to CYear (C for calendar) to differentiate it from Fiscal Year. Additionally, I noticed that Chris’s code has an off-by-one issue which means that you won’t get the final day of your calendar. I addressed this by adding one day to NumberOfDates calculation:

    NumberOfDates = 1 + Duration.Days( EndDate – StartDate ),

With the Bounds table complete, Chris’s main function and calling function can now be utilized to produce the Base Date Table. (I edit Chris’s calling function to reference Bounds, instead of the Excel file his function uses.) That’s just the beginning, however! The Base Date Table is sufficient for standard calendars, but doesn’t cut it in a 445 world! Next we add the columns that enable 445 calculations to work!

Phase II – The 445 enhancements

Much of what I add here corresponds to existing columns in the Base Date Table, but is based, instead, on the start and end dates from Periods. For any given calendar day, I need to know where it stands in the periods and quarters of the 445 fiscal calendar.

I start of by creating a new query, FDate, referencing the Base Date Table. Step one is to add the fiscal period data to each row in the Base Date Table. This is accomplished by: making a new query referencing Base Date Table; using Merge Queries to do a left outer join from [Date] in Base Date Table to [Start Date] in Periods; and then expanding the resulting column of tables. What you see at this point is a bunch of new columns with nulls, and the occasional row where those new columns contain the fiscal data from Periods (fiscal year, period number, the start and end date of each period). Just to be explicit, I rename the Year column to FYear – F for fiscal.

For reasons unknown, expanding the new column scrambles the sort order of the Base Date Table, necessitating an ascending sort on the original date column to get the rows back in proper order. Having a correctly-sorted date table is essential in order to address the nulls. Since every day in a particular period has the same (fiscal) year, period number, period start and period end date, I need to propagate the values from the first day of the period all the way down to and including the final day of the period. This is easily accomplished with Fill Down, a useful feature I learned about from my colleague Brian Grant’s recent blog and meetup presentation on Transforming Nested Tables in Power Query.

From here on, additions to the fiscal table can be logically categorized into several buckets: simple dates, offsets, counts and descriptive columns. The M code is included below, but here is a high-level summary of each addition and how it’s calculated. Please note, due to dependencies, the implementation order is somewhat different.

Dates

• FQtr Start. Seeded by populating a new column with values only where [PeriodNumOfQtr] = 1 and [DayOfPeriod] = 1, then using FillDown to propagate that date to the rest of the quarter.
• FYear Start. Seeded by populating a new column with values only where [Period] = 1 and [DayOfPeriod] = 1, then using FillDown to propagate that date to the rest of the year.

Offsets. How far along in the period/quarter/year is this date?

• Period Of Qtr (1-3). Calculated using modulo arithmetic on Period.
• FQtr (1-4). Calculated using a combination of rounding, addition and division on [Period] to determine the quarter to which this row belongs.
• Day Of Period (typically 1-35). Derived by subtracting the period start date from the row date, plus 1.
• Day Of Year (typically 1-364). Derived by subtracting FYear Start from the row date, plus 1.
• Day Of Qtr (typically 1-91). Derived by subtracting FQtr Start from the row date, plus 1.

Counts

• Days In Period (typically 28 or 35). Derived by subtracting the period start date from the period end date, plus 1.

Descriptive (text) columns. Fiscal calendars, by their nature, are very numeric-centric. This can lead to confusion between quarters & periods. These last several columns give you the ability to provide visuals with a bit more context than just 1, 2, 3… Depending on your taste for hyphens, update accordingly.

• Period Name. Prefix the period number with P (P0 if the period number is single-digit, so that sorting works appropriately).
• Year-Period. Concatenate FYear with Period Name, i.e., 2016-P04.
• FQtr Name. Prefix FQtr with ‘Q’, i.e., Q4.
• Year-FQtr. Concatenate FYear with FQtr Name, i.e., 2016-Q4.

Finishing Touches

As only the final fiscal date calendar is needed, I right-click each of my preliminary tables and clear the check mark by Enable Load, leaving only FDate visible to my users.

After clicking Close & Apply the Power Query, I also recommend you go through each of the columns ensuring that they have the correct data type, and for text columns, assigning a Sort By Column. Strictly speaking, I guess the Sort By isn’t necessary in the 445 world (P01, P02… P10 should sort correctly), but habits die hard.

Enjoy!

445 Power Query code

let

    Source = BaseDateTable,
    Merge_to_Periods = Table.NestedJoin(BaseDateTable,{"Date"},Periods,{"Start Date"},"NewColumn",JoinKind.LeftOuter),
    Sort_Rows = Table.Sort(Merge_to_Periods,{{"Date", Order.Ascending}}),
    Expand_NewColumn = Table.ExpandTableColumn(Sort_Rows, "NewColumn", {"Year", "Period", "Start Date", "End Date"}, {"Year.1", "Period", "Start Date", "End Date"}),
    Rename_Years = Table.RenameColumns(Expand_NewColumn,{{"Year", "CYear"}, {"Year.1", "FYear"}}),
    Fill_Down = Table.FillDown(Rename_Years,{"Date", "FYear", "Period", "Start Date", "End Date"}),
    Add_Period_Of_Qtr = Table.AddColumn(Fill_Down, "Period Of Qtr", each if (Number.Mod([Period],3)) = 0 then 3 else Number.Mod([Period],3)),
    Add_FQtr = Table.AddColumn(Add_Period_Of_Qtr, "FQtr", each Number.RoundDown ( ( [Period] + 2 )/3)),
    Add_Day_Of_Period = Table.AddColumn(Add_FQtr, "Day Of Period", each 1 + Duration.Days ( Duration.From ( [Date] - [Start Date] ) )),
    Add_FYear_Start = Table.AddColumn(Add_Day_Of_Period, "FYear Start", each if [Period] = 1 and [Day Of Period] = 1 then [Date] else null),
    Add_FQtr_Start = Table.AddColumn(Add_FYear_Start, "FQtr Start", each if [Period Of Qtr] = 1 and [Day Of Period] = 1 then [Date] else null),
    Fill_Down_Again = Table.FillDown(Add_FQtr_Start,{"FYear Start", "FQtr Start"}),
    Add_Day_Of_Year = Table.AddColumn(Fill_Down_Again, "Day Of Year", each 1 + Duration.Days ( Duration.From ( [Date] - [FYear Start] ) )),
    Add_Day_Of_Qtr = Table.AddColumn(Add_Day_Of_Year, "Day Of Qtr", each 1 + Duration.Days ( Duration.From ( [Date] - [FQtr Start] ) )),
    Add_Days_In_Period = Table.AddColumn(Add_Day_Of_Qtr, "Days In Period", each 1 + Duration.Days ( Duration.From ( [End Date] - [Start Date] ) )),
    Add_Period_Name = Table.AddColumn(Add_Days_In_Period, "Period Name", each if [Period] < 10 then "P0" & Text.From ( [Period] ) else "P" & Text.From ( [Period] )),
    Add_Year_Period = Table.AddColumn(Add_Period_Name, "Year-Period", each Text.From( [FYear] ) & "-" & [Period Name]),
    Add_FQtr_Name = Table.AddColumn(Add_Year_Period, "FQtr Name", each "Q" & Text.From ( [FQtr] )),
    Add_FYear_Qtr = Table.AddColumn(Add_FQtr_Name, "FYear-Qtr", each Text.From( [FYear] ) & "-" & [FQtr Name])
in
    Add_FYear_Qtr