Create Calendar Table and Calculate Total Sales by Fiscal Period in Power Pivot Data Model

Welcome to Excel Jet Consult blog post. In this Excel tutorial, we will learn how to create calendar table and calculate total sales by fiscal period in Power Pivot Data Model. Let’s get started

Sample Data and Loading to the Data Model

In the screenshot below, we’ve got a sales dataset that is officially stored as an Excel Table using CTRL + T. The first thing we want to do is to get the dataset into the Power Pivot data model. To do that:

In the Tables group of the Pivot Pivot tab, click on Add to Data Model

Total Sales DAX Measure

The data is loaded into the data model. The next thing we want to do is create a measure called Total Sales. To do that:

Select any cell in the measure grid below the dataset
In the formula bar, execute the DAX formula: Total Sales:=SUM(facttbl[Total])
Click Enter
Apply currency formatting in the Formatting group of the Data Model Home tab

From the screenshot below, we have the total sales measure in the grid cell

Create Calendar Table

Since we want to calculate the Total Sales by Fiscal Period, it is important to create what is called a Calendar Table that will return all the possible dates from the Date column in the facttbl table. Then, we are going to create additional Calculated Columns for the standard quarter, fiscal quarter, fiscal year, and the fiscal period which we are going to use with the Total Sales DAX measure in the our PivotTable report. In Excel Power Pivot, the Calendar table is created automatically upon clicking on few buttons. To create the calendar table:

In the Calendars group of the Power Pivot Design table, click on Date Table and select New.

From the screenshot below, we can see the calendar table create. Though, I have deleted three columns that is not necessary for this exercise

Calculated Column for Quarter, Fiscal Quarter, Fiscal Year, Fiscal Period & FP Sort Helper Column

The first calculated column we want to create in the Calendar Table is standard Quarter. To do that:

Double click on Add Column and name as Quarter
In the formula bar, execute this DAX formula: =ROUNDUP(‘Calendar'[Month Number]/3,0)
Click Enter.

To calculate the Fiscal Quarter, double-click on Add Column and rename as Fiscal Quarter

In the formula bar, execute the DAX formula: =IF(‘Calendar'[Quarter]=1,4,’Calendar'[Quarter]-1). Note. In this exercise, the fiscal year starts from April 1st of the current year and ends on March 31st of the next year
Click Enter. From the screenshot below, we can see that the fiscal period starts in April

To calculate the Fiscal Year, double-click on Add Column and rename as Fiscal Year
In the formula bar, execute the DAX formula: =IF(‘Calendar'[Quarter]=1,’Calendar'[Year]-1,’Calendar'[Year])
Click Enter.

To calculate the Fiscal Period, double-click on Add Column and rename as Fiscal Period
In the formula bar, execute the DAX formula: =“Q”&’Calendar'[Fiscal Quarter]&“-“&’Calendar'[Fiscal Year]
Click Enter.

Having created the fiscal period, it is important to create helper column that we can use to sort the Fiscal Period chronologically. To create the Fiscal Period Sort:

Double-click on Add Column and rename as Fiscal Period Sort
In the formula bar, execute the DAX formula: =’Calendar'[Fiscal Year]*10+’Calendar'[Fiscal Quarter]
Click Enter.

Then, to sort the Fiscal Period field using the FP Sort:

Select the Fiscal Period column and in the Sort & Filter group of the Home tab, select Sort by Column
In the Sort by Column dialogue box, under the Sort Column dropdown, select Fiscal Period and under the By Column dropdown, select Sort FP
Click OK

Create Relationship Between the Facttbl and the New Calendar Table

The next import step we need to take is to create a one to many relationship between the facttbl and the calendar table. To do that:

In the View group of the Home tab, click on Diagram View
Drag and connect the Date field in the Calendar table to the Order Date field in the facttbl as seen in the screenshots below