Mastering Fiscal Periods: Create a Flexible Calendar Table in Power Pivot
When working with large data sets with multiple dates, it is important to find trends by comparing historical data. A calendar table is the best tool for analyzing time series data as it provides an easy way to navigate and compare historical dates. It is best practice to have a calendar table in your data model, this is because a calendar table always contains a complete set of dates that mostly feeds from all fact tables of the data model without duplicates or missing values.
Characteristics of a Good Calendar Table
The dates must be in ascending order without gaps or duplicates
Ideally, the earliest date in the calendar table must be before or equal to the earliest date in the fact table within the data model
Similarly, the latest date of the calendar table should be on or after the latest date in the fact table within the data model.
In the snapshot below, we have a SalesData table that spans from 1/Dec/2020 to 20/May/2021. Therefore, in our calendar table, the earliest date should start on or before 1/Dec/2020, and the latest date should be on or after 20/May 2021.
Next, we will head to Power Pivot to add this dataset to the model and create a Calendar table. To do this, refer to the Design tab > Date Table > New
Creating a Date Table
A Calendar table will be created automatically if the date on the first column is not accurate, you can edit it by using the Update Range feature and typing in the correct date.
Update the date range if need be
The Calendar will look a lot like this:
Standard Data Table
Although Power Pivot has done a great job in providing the extra columns, it may not be sufficient if your organization has a peculiar timeline for your Operational/Financial year.
Let’s assume that in our Organization, the fiscal year ends in March and Starts in April each year. We will need some calculated columns using DAX to get this. The columns we will be creating are:
Calendar Quarter as Qtr
Fiscal Quarter (assuming the Fiscal year starts in April)
Fiscal Year (Called “FY”)
Fiscal Period (Called “FP”)
To create these calculated columns, we will need the following DAX functions
Qtr: =ROUNDUP('Calendar'[Month Number]/3,0)
FQtr: = IF('Calendar'[Qtr] = 1, 4, 'Calendar'[Qtr]-1)
FY: =IF('Calendar'[Qtr]=1, 'Calendar'[Year]-1, 'Calendar'[Year])
FP: ="Q"&'Calendar'[FQtr]&"-"&'Calendar'[FY]
The result should as shown below
Calendar table with flexible fiscal periods
Note that, to enable time series functions in Power Pivot, calendar tables must be marked as date tables. The highlighted calendar table has been automatically designated as a date table. You can verify this by going to the Design Ribbon and selecting "Mark as Date Table" as seen below.
Next, we need to update our data model and connect the SalesData (Table1_1) to the Calendar Table. Navigate to the Diagram View on the Home tab and connect both tables by dragging the data column from the calendar table and dropping on the OrderedAt Date column.
Diagram view
Now that we have the calendar table ready for use, let’s test it out. Navigate to the Home tab and click on PivotTable to create a pivot table.
Drag the FP column to the rows section of the pivot table. You may observe that the values are currently sorted in ascending order, meaning all the Q1 values appear first. However, this sorting is incorrect. To rectify this, we should arrange the date by quarter/period, ensuring that the earliest quarter/period appears first.
To solve this, first, we need to create an additional column called “Sort FP” to sort the Fiscal Period Column by using this formula: ='Calendar'[FY]*10+'Calendar'[FQtr].
After the column is created, we then sort the FP column by the Sort FP column.
And that solves it!
Hope you’ve learned a thing or two from this article. I’d like to read from you in the comments.
