This article delves into how to create a 445 calendar in Power Query. You’ll learn how to set up the date column, create indexes like weeks, periods, and quarters. We’ll also show you how to create calculations related to years, quarters, weeks and periods. And best of all, you can also use this pattern for the 445, 454 and 544 variations.

If you prefer video, you can watch the tutorial here:

This article is part of the Date Table series, which consists of:

## Date Table Series

### Foundations

**Creating a (Dynamic) Date Table**

**Build a Calendar that supports Multiple Languages**

**Creating a 445 Calendar (incl 454 and 544)**

### Advanced Topics

**Create ISO Week and Year Column**

**Return Nth Business Day From Date**

**Compute Working Days Between Dates**

**Creating Ordinal Numbers (1st, 2nd, 3rd etc.)**

**Create Default Slicer Value of Current Month**

### Julian Dates

Before we get started, I’d like to give a shout-out to Ken Puls. Some fundamental calculations in this article are based on his earlier work and the book Master your Data with Power Query. Great work Ken! Having said that, let’s get started!

**Table of contents**

## What is a 445 Calendar?

A 445 calendar is a special calendar to standardize financial reporting. It’s often used by retail businesses. Unlike the regular calendar where months have varying lengths (28 to 31 days), the 445 calendar divides the year into 52 weeks, with four quarters. Each quarter has three periods, with the first and second period having 4 weeks each, and the third period having 5 weeks. This makes every quarter a consistent 13 weeks.

The main difference from a regular calendar is this regularity. With the 445 calendar, each period has the same number of weeks, making it easier to compare different periods. In contrast, a standard calendar is inconsistent because some months have a different number of days.

## Benefits of a 445 Calendar

Many retail businesses prefer the 445 calendar for several reasons:

**Consistent Reporting Periods**: When comparing years each period, quarter and year has the same number of days. That makes it easy to compare metrics across different periods without worrying about the variability in the number of days in each month.**Weekday Consistency**: The 445 calendar makes sure that week, period and quarter and year comparison uses the same day of the weeks. It simplifies the analysis of weekly and daily trends.

## Financial Calendars Versions

The 445 Calendar has different versions. Here’s the ones that you can find:

**445 Calendar**: Each quarter consists of two 4-week periods, followed by a 5-week period.**454 Calendar**: Has four weeks in the first period, five weeks in the second period, and four weeks in the third period of each quarter.**544 Calendar**: Has five weeks in the first month, four weeks in the second month, and four weeks in the third month of each quarter.**13-Period Calendar**: The year is divided into 13 equal periods, each consisting of 4 weeks.

In this blogpost, we focus on the first three versions. So, how can we build a custom 445 calendar?

## Building the Foundation

Each calendar starts with a column of dates. So let’s see what considerations are important here.

### Setting the Correct Starting Date

First of all, it’s important to set correct start date when creating a custom calendar for several reasons:

**Alignment with Fiscal Year**: The start date should align with the beginning of the fiscal year to make sure it complies with your company’s financial reporting / accounting standards.**Consistency in Reporting**: A correctly set start date guarantees that each fiscal year and quarter starts on the same day of the week.

### Creating a list of dates

To set the start date, you can create a step called `StartDate`

, where the user can specify the date where the calendar starts. Generally this date starts on a Monday.

With the `StartDate`

parameter in place, we can now generate a list of dates. If you want to make it run until the end of the current year, you can define the `EndDate`

parameter as:

`Date.EndOfYear( DateTime.LocalNow() )`

To create a list of dates you then use:

```
List.Dates(
StartDate,
Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)
)
```

Once the list of dates has been created, you can now transform it into a table and we have the base for our 445 calendar.

## Index Calculations

After you’ve created a column of dates, the next step is to build the most important columns of your calendar, the indexes. These include Day, Week, Period, and Quarter Index columns. These indexes makes it easy to track the progression of time in your calendar and we can base our calculations on them. Let’s see how we can create them.

### Creating the Day Index

The Day Index is a sequential number that increments by one for each day in your calendar. This index forms the foundation for all other time-related calculations. You can create the Day Index using the Table.AddIndexColumn function:

`Table.AddIndexColumn( myDateTable, "FW_DayIndex", 1, 1, Int64.Type)`

This function starts with a value of 1 for the first day in your calendar and increases the value by 1 for each subsequent day. This simple index helps to uniquely identify each day.

### Creating the Week Index

Once you have your Day Index, you can **calculate a Week Index** to identify which week each date falls into. The formula for this is:

`Number.RoundUp( [FW_DayIndex] / 7 )`

Here’s how it works:

- The formula takes the Day Index and divides it by 7 (the number of days in a week).
- Finally, it rounds up the division result to get the correct week number.

This approach ensures that each week has its unique identifier, starting from 1 for the first week.

### Creating the Period Index

The Period Index is a bit more complex because it depends on the type of calendar you’re using—whether it’s a 445, 454, or 544 calendar. These calendars have different patterns for the number of weeks in each period.

To create the Period Index, you’ll need to introduce a variable that specifies the type of calendar. Here’s an example using a 445 calendar:

```
[
// Specify the calendar type: 445, 454, or 544
Order445 = { 4, 4, 5 },
// Calculate week number within current 13-week cycle (1-based index)
WeekInCycle = Number.Mod( ( [FW_WeekIndex] - 1), 13 ) + 1,
WeeksInFirstPeriod = List.First( Order445 ),
WeeksUpToSecondPeriod = List.Sum( List.FirstN( Order445, 2 ) ),
// Determine in which Cycle of Periods the current week falls.
PeriodInCycle = if WeekInCycle <= WeeksInFirstPeriod then 1 else
if WeekInCycle <= WeeksUpToSecondPeriod then 2 else 3,
// Calculate the number of complete 13-week cycles that have passed
CycleIndex = Number.RoundDown( ( [FW_WeekIndex] - 1) / 13 ),
/* Compute the final PeriodIndex by combining the cycle index
and Period within the cycle. Each cycle consists of 3 Periods,
so multiply the cycle index by 3 and add the Period in cycle */
Result = CycleIndex * 3 + PeriodInCycle
]
[Result]
```

Here’s a breakdown of the steps:

**Order445:**Defines the pattern of weeks in a 445 calendar (4 weeks, 4 weeks, then 5 weeks).**WeekInCycle:**Determines the current week within a 13-week cycle.**WeeksInFirstPeriod/WeeksUpToSecondPeriod:**These variables help to determine which period the current week belongs to.**PeriodInCycle:**Identifies whether the current week is in the first, second, or third period.**CycleIndex:**Counts how many 13-week cycles have been completed.**Result:**Combines the cycle and period information to produce the final Period Index.

The benefit of this approach is that it’s flexible for any version of the 445 calendar.

### Creating the Quarter Index

With the Day Index in place, **calculating the Quarter Index** is straightforward. You simply divide the Day Index by 91 (since a quarter in this calendar consists of 91 days) and round up:

`Number.RoundUp( [FW_DayIndex] / 91 )`

This formula ensures that every group of 91 days is assigned to a distinct quarter, making it easy to track performance or trends on a quarterly basis.

### Creating the Year Index

Finally, you might find it useful to have a Year Index, especially when working with multiple years. The logic is similar to the Quarter Index, but you’ll divide by 364 days (the number of days in a year for a 445 calendar):

`Number.RoundUp( [FW_DayIndex] / 364 )`

This index helps to group dates by year, which can be particularly useful in long-term trend analysis.

## Year Related Calculations

In this section, you’ll learn how to perform various calculations related to the fiscal year. These calculations include determining the Fiscal Day of the Year, the Fiscal Year itself, and creating helpful labels to clearly present these dates.

Here’s a quick overview of year-related fields you could run into:

Let’s see how we can create some of these fields.

### Calculating the Fiscal Day of the Year

The Fiscal Day of the Year is a sequential number that identifies which day of the fiscal year a particular date falls on. You can calculate it using the following formula:

`Number.Mod( [FW_DayIndex] - 1, 364 ) + 1`

Here’s how it works:

**[FW_DayIndex] – 1**: Adjusts the Day Index to start from zero.**Number.Mod(…, 364)**: Uses the modulo operation to wrap the day index within a 364-day year.**+ 1**: Adds 1 to ensure the count starts from 1 for the first day of the fiscal year.

This calculation gives you a day number from 1 to 364, which corresponds to each day within your fiscal year.

### Determining the Fiscal Year

Next, you’ll need to **calculate the Fiscal Year** for each date. This can be done using the following formula:

`Date.Year( StartDate ) + [FW_YearIndex] - 1`

Explanation:

**Date.Year( StartDate )**: Extracts the year from your fiscal calendar’s start date.- +
**[FW_YearIndex] – 1**: Adjusts the year index to align with the fiscal year cycle.

By adding the year index to the start year, you can determine the correct fiscal year for each date in your table.

### Adding a Fiscal Year Label

To make it clearer we’re dealing with a Fiscal Year, it may be useful to add a label to your calculations. Since this calendar deals with Fiscal Weeks (FW), we can prefix the year with the abbreviation “FW Y”

`"FW Y " & Text.From( [FW_Year] )`

This formula prepends “FW Y” to the fiscal year, resulting in labels such as “FW Y 2023.” These labels are especially useful for reports and dashboards where clarity is key.

### Calculating the Start of the Fiscal Year

Knowing the start date of each fiscal year is important for many calculations. You can find the start of the fiscal year using this formula:

`Date.AddDays( [Date], - ( [FW_DayOfYear] - 1 ) )`

Explanation:

**[Date]**: The current date in your calendar.**[FW_DayOfYear] – 1**: Subtracts 1 from the Day Of Year number to compute the number of days to subtract from the current date to end up at the start of the year.**Date.AddDays(…)**: Adjusts the date backwards to the start of the fiscal year.

This formula calculates the date of the first day of the fiscal year that the current date belongs to.

### Calculating the End of the Fiscal Year

Similarly, you can calculate the end of the fiscal year. The formula takes the start of the fiscal year, and simply adds 363 days to it:

`Date.AddDays( [FW_StartOfYear], 364 - 1 )`

This calculation provides the year in which the fiscal year ends, which can be helpful in your calculations.

### Creating a Fiscal Year Range Label

Finally, to add more context to your fiscal years, I like adding a label that includes the start and end dates of each fiscal year. Here’s how you can create such a label:

```
[FW_YearLabel]
& ": "
& Date.ToText( [FW_StartOfYear], [ Format = "dd MMM yyyy", Culture = CultureCode ] )
& " - "
& Date.ToText( [FW_EndOfYear], [ Format = "dd MMM yyyy", Culture = CultureCode ] )
```

Explanation:

**[FW_YearLabel]**: The fiscal year label we created earlier (e.g., “FW Y 2023”).**Date.ToText(…)**: Converts the start and end dates of the fiscal year into text, formatted as “dd MMM yyyy” (e.g., “01 Jan 2023”).**& ” – “**: Adds a separator between the start and end dates.

This formula produces a descriptive label like “FW Y 2023: 01 Jan 2023 – 31 Dec 2023,” which makes it easy to quickly understand the fiscal year range. In a matrix visual you could use it as follows:

## Quarter Related Calculations

Quarters are also important for segmenting your 445 calendar into manageable parts. Each quarter in a 445 calendar consists of 13 weeks. In this section, you’ll learn how to calculate various quarter-related values and create descriptive labels to improve readability.

Here’s a quick overview of quarter related columns you may find in your 445 calendar:

### Calculating the Fiscal Quarter

To determine which fiscal quarter a particular date belongs to, you can use the following formula:

`Number.Mod( [FW_QuarterIndex] - 1, 4 ) + 1`

Explanation:

**[FW_QuarterIndex] – 1**: Adjusts the quarter index to start from zero.**Number.Mod(…, 4)**: Applies a modulo operation to wrap the quarter index within the 4 quarters of a year.**+ 1**: Makes sure that the count starts from 1 for the first quarter.

This calculation assigns each date to its corresponding fiscal quarter, returning values from 1 to 4.

### Adding a Fiscal Quarter Label

To make it clear for readers they’re dealing with quarters, it’s helpful to label each quarter explicitly. You can create a label that combines a prefix (like “FW Q”) with the calculated quarter number:

`"FW Q" & Text.From( [FW_Quarter] )`

This formula generates labels such as “FW Q1” or “FW Q2,” which make it clear which fiscal quarter each date belongs to.

### Calculating the Day of the Quarter

Understanding where a specific day falls within a quarter can be important for more specific requirements. You can calculate the Day of the Quarter using this formula:

`Number.Mod( [FW_DayOfYear] - 1, 91 ) + 1`

Explanation:

**[FW_DayOfYear] – 1**: Adjusts the Day of the Year index to start from zero.**Number.Mod(…, 91)**: Applies a modulo operation to wrap the day within a 91-day quarter.**+ 1**: Makes sure that the count starts from 1 for the first day of the quarter.

### Determining the Start of the Quarter

To find the start date of the quarter for any given date, use the following formula:

`Date.AddDays( [Date], - ( [FW_DayOfQuarter] - 1 ) )`

Explanation:

**[Date]**: The current date in your calendar.**[FW_DayOfQuarter] – 1**: Subtracts 1 to create the number of days to subtract from the current date to reach the start of the quarter.**Date.AddDays(…)**: Adjusts the date backwards to the first day of the quarter.

This formula finds the start date of the quarter that the current date belongs to.

### Determining the End of the Quarter

Once you have the start date of the quarter, calculating the end date is simple. You can do this with the following formula:

`Date.AddDays( [FW_StartOfQuarter], 91 - 1 )`

Explanation:

**[FW_StartOfQuarter]**: The start date of the quarter, calculated in the previous step.**91 – 1**: Adds 90 days to the start date to reach the last day of the 91-day quarter.

This formula gives you the end date of the quarter, allowing you to define the full date range of each quarter.

### Creating a Quarter Range Label

Finally, it’s helpful to create a label that clearly shows the date range each quarter covers. This can be done with the following formula:

```
[FW_QuarterLabel]
& ": "
& Date.ToText( [FW_StartOfQuarter],
[Format = "MMM yyyy", Culture = CultureCode ] )
& " - "
& Date.ToText( [FW_EndOfQuarter],
[Format = "MMM yyyy", Culture = CultureCode ] )
```

Explanation:

**[FW_QuarterLabel]**: The quarter label you created earlier (e.g., “FW Q1”).**Date.ToText(…)**: Converts the start and end dates of the quarter into text, formatted as “MMM yyyy” (e.g., “Jan 2023”).**& ” – “**: Adds a separator between the start and end dates.

This formula creates a descriptive label such as “FW Q1: Jan 2023 – Mar 2023,” making it easy to see the full span of each fiscal quarter. Here’s an example of how you could use it in a visual:

## Week Related Calculations

Weeks are important units of time in a 445 calendar, and calculating week-related values helps organizing your data. In this section, you’ll learn how to calculate the fiscal week number, create descriptive week labels, and determine the start and end dates of each week.

The most important week related calculations are:

Here’s how you can create some of these.

### Calculating the Fiscal Week Number

The Fiscal Week Number identifies the specific week within a fiscal year. You can calculate this by using the Week Index and performing a modulo operation with 52 weeks, as follows:

`Number.Mod( [FW_WeekIndex] - 1, 52 ) + 1`

Explanation:

**[FW_WeekIndex] – 1**: Adjusts the Week Index to start from zero.**Number.Mod(…, 52)**: Applies a modulo operation to wrap the week index within the 52 weeks of a fiscal year.**+ 1**: Makes sure that the count starts from 1 for the first week.

This formula returns a week number between 1 and 52, representing the specific week within the fiscal year.

### Adding a Fiscal Week Label

To make your data more user-friendly, you can create a label for each fiscal week. This label combines a prefix (like “FW W”) with the calculated week number, ensuring it always has two digits:

`"FW W" & Text.PadStart( Text.From( [FW_Week] ), 2, "0" )`

Explanation:

**“FW W”**: Adds a prefix to indicate the fiscal week.**Text.From( [FW_Week] )**: Converts the week number to text.**Text.PadStart(…, 2, “0”)**: Makes sure that the week number is two digits, padding with a leading zero if necessary (e.g., “FW W01”, “FW W12”).

This labeling makes it easier to identify and reference specific weeks in your reports and dashboards.

### Calculating the Week of the Quarter

Each quarter in a 445 calendar contains 13 weeks, and it is sometimes useful to know which week of the quarter a particular date falls into. You can calculate this using the following formula:

`Number.Mod( [FW_Week] - 1, 13 ) + 1`

Explanation:

**[FW_Week] – 1**: Adjusts the Week Number to start from zero.**Number.Mod(…, 13)**: Applies a modulo operation to wrap the week number within the 13 weeks of a quarter.**+ 1**: Ensures that the count starts from 1 for the first week of the quarter.

This calculation returns a number between 1 and 13, identifying the specific week within the quarter.

### Determining the Start of the Week

To calculate the start date of a given week, you can use the Date.StartOfWeek function. By default, this function assumes the week starts on Monday, but you can adjust it if your week starts on a different day:

`Date.StartOfWeek( [Date], Day.Monday )`

This works as long as your week starts on monday. In case you work starts on a different day, you can swap the Day Type enumeration to the desired start day.

### Determining the End of the Week

Similarly, you can calculate the end date of the week using the Date.EndOfWeek function:

`Date.EndOfWeek( [Date], Day.Monday )`

This function returns the date of the last day of the week (e.g., Sunday if the week starts on Monday).

### Creating a Week Range Label

To provide a clear representation of each week’s date range, you can create a descriptive label that includes both the start and end dates of the week:

```
[FW_WeekLabel]
& ": "
& Date.ToText( [StartOfWeek],
[Format = "dd MMM yyyy", Culture = CultureCode ] )
& " - "
& Date.ToText( [EndOfWeek],
[Format = "dd MMM yyyy", Culture = CultureCode ] )
```

Explanation:

**[FW_WeekLabel]**: The fiscal week label you created earlier (e.g., “FW W01”).**Date.ToText(…)**: Converts the start and end dates of the week into text, formatted as “dd MMM yyyy” (e.g., “01 Jan 2023”).**& ” – “**: Adds a separator between the start and end dates.

This formula creates a label like “FW W01: 01 Jan 2023 – 07 Jan 2023,” clearly showing the full date range for each week. An example of how you can use it is:

## Period Related Calculations

In a 445 calendar, instead of using traditional months, the year is divided into periods of 4 or 5 weeks. Each quarter typically consists of three periods, with the length of periods varying based on the 445, 454, or 544 pattern. In this section, you’ll learn how to create and work with these periods.

Here’s a quick overview of the most important period related fields in a calendar:

Let’s see how we can create some of these.

### Creating the Period Number

To determine the Period number within the fiscal year, you can use the `PeriodIndex`

that was calculated earlier. This index already accounts for the different versions of the 445 calendar, making it flexible for each calendar version. Here’s how you can calculate the Period number:

`Number.Mod( [FW_PeriodIndex] - 1, 12 ) + 1`

Explanation:

**[FW_PeriodIndex] – 1**: Adjusts the Period Index to start from zero.**Number.Mod(…, 12)**: Applies a modulo operation to make sure that the period wraps within the 12 periods of a fiscal year.**+ 1**: Makes sure the count starts from 1, aligning with the first period.

This formula gives you a Period number between 1 and 12, representing each period within the fiscal year.

### Creating a Period Label

Just like with weeks and quarters, it’s helpful to label the periods clearly. You can create a label that combines a prefix (like “FW P”) with a period number that always has two digits:

`"FW P" & Text.PadStart( Text.From( [FW_Period] ), 2, "0" )`

Explanation:

**“FW P”**: Adds a prefix to indicate the fiscal period.**Text.From([FW_Period])**: Converts the period number to text.**Text.PadStart(…, 2, “0”)**: Ensures that the period number is two digits, padding with a leading zero if necessary (e.g., “FW P01”, “FW P02”).

This labeling makes it easy for readers to see the start and end of a period.

### Determining the Period Within a Quarter

To identify which period within the quarter a particular date belongs to (out of the three possible periods), you can use the following formula:

`Number.Mod( [FW_Period] - 1, 3 ) + 1`

Explanation:

**[FW_Period] – 1**: Adjusts the Period number to start from zero.**Number.Mod(…, 3)**: Applies a modulo operation to wrap the period number within the three periods of a quarter.**+ 1**: Makes sure the count starts from 1.

This calculation gives you a number between 1 and 3, indicating which period of the quarter the date belongs to.

### Calculating the Day of the Period

To determine the specific day within a period (which can range up to either 28 or 35 days), you can use the following approach:

```
let
Order445 = List.Buffer( OrderOf445 ),
DaysInFirstPeriod = Order445{0} * 7,
DaysUpToSecondPeriod = List.Sum( List.FirstN( Order445, 2 ) ) * 7 ,
myRecord = [1 = 0, 2 = DaysInFirstPeriod, 3 = DaysUpToSecondPeriod ],
result = [FW_DayOfQuarter]
- Record.Field( myRecord, Text.From( [FW_PeriodOfQuarter] ) )
in result
```

Explanation:

**Order445**: Holds the week pattern of the 445 calendar.**DaysInFirstPeriod**: Calculates the total days in the first period.**DaysUpToSecondPeriod**: Calculates the total days up to the end of the second period.**myRecord**: A record to map the days corresponding to the first and second periods.**result**: Subtracts the calculated days from the Day of the Quarter to determine the Day of the Period.

This logic calculates the exact day within the period, which you may find useful for your analysis.

### Calculating the Total Number of Days in the Period

If you only need to know the total number of days in the current period (either 28 or 35), you can calculate this with the following code:

```
[
Order445 = { 4, 4, 5 },
PositionOf5 = List.PositionOf( Order445, 5 ),
StartSum = List.Sum( List.FirstN( Order445, PositionOf5 + 1 ) ),
Result = if [FW_WeekOfQuarter] > StartSum - 5
and [FW_WeekOfQuarter] <= StartSum then 35 else 28
]
[Result]
```

Explanation:

**PositionOf5**: Finds the position of the 5-week period in the 445 pattern.**StartSum**: Sums the weeks up to and including the 5-week period.**Result**: Determines whether the current period is 35 days or 28 days long based on its position in the quarter.

### Determining the Start of the Period

To calculate the start date of a period, use the following formula:

`Date.AddDays( [Date], -( [FW_DayOfPeriod] - 1 ) )`

Explanation:

**[Date]**: The current date in your calendar.**[FW_DayOfPeriod] – 1**: Returns the number of days to subtract from the date to reach the start of the period.**Date.AddDays(…)**: Moves the current date backwards to the start of the period.

This function returns the start date of the period.

### Determining the End of the Period

To calculate the end date of the period, add the total number of days in the period (minus 1) to the start date:

`Date.AddDays( [FW_StartOfPeriod], [FW_DaysInPeriod] - 1 )`

Explanation:

**[FW_StartOfPeriod]**: The start date of the period.**[FW_DaysInPeriod] – 1**: Adds the period length, minus one day, to reach the last day of the period.

This formula gives you the end date of the period, allowing you to define the full date range of each period.

### Creating a Period Range Label

To make it explicit what dates belong to your period, you can create a label that specifies the date range each period covers:

```
FW_PeriodLabel]
& ": "
& Date.ToText([FW_StartOfPeriod],
[Format = "dd MMM yyyy", Culture = CultureCode ] )
& " - "
& Date.ToText([FW_EndOfPeriod],
[Format = "dd MMM yyyy", Culture = CultureCode ] )
```

Explanation:

**[FW_PeriodLabel]**: The period label you created earlier (e.g., “FW P01”).**Date.ToText(…)**: Converts the start and end dates of the period into text, formatted as “dd MMM yyyy” (e.g., “01 Jan 2023”).**& ” – “**: Adds a separator between the start and end dates.

This label, such as “FW P01: 01 Jan 2023 – 28 Jan 2023,” helps understand the full date range of each period. This label is helpful for readers to know exactly what period they’re dealing with:

### Determining the Week Within a Period

Lastly, you may want to identify the specific week within a period. You can calculate this by dividing the Day of the Period by 7 and rounding up:

`Number.RoundUp( [FW_DayOfPeriod] / 7 )`

Explanation:

**[FW_DayOfPeriod] / 7**: Divides the day number by 7 to determine the week number.**Number.RoundUp(…)**: Rounds up the result to ensure that even partial weeks are counted as full weeks.

This formula gives you the week number within the period, helping you track more detailed time intervals.

## Conclusion

In this article, we delved into the most important elements of the 445, 454 and 544 calendar. As you’ve seen, the index numbers form the foundation of most calculations. With those in place, we used calculations that make use of the fixed lengths of years, quarters, weeks and applied some logic for the 445 periods. While there may be other fields you could use, this article provided you the most important ones.

Enjoy Power Query!

Is there a future update where you might also be able to show how you would handle a 53rd week on these 445/454/544 calendars that happens every 5 or so years?