Extending the Data Model

In this chapter, you will learn how to extend the data model created in the previous chapters. Extending a data model includes −

• Addition of tables
• Addition of calculated columns in an existing table
• Creation of measures in an existing table

Of these, creating the measures is crucial, as it involves providing new data insights in the data model that will enable those using the data model avoid rework and also save time while analyzing the data and decision making.

As Profit and Loss Analysis involves working with time periods and you will be using DAX Time Intelligence functions, you require a Date table in the data model.

If you are new to Date tables, go through the chapter – Understanding Date Tables.

You can extend the data model as follows −

• To create a relationship between the data table, i.e. Finance Data table and the Date table, you need to create a calculated column Date in the Finance Data table.

• To perform different types of calculations, you need to create relationships between the data table - Finance Data and the lookup tables – Accounts and Geography Locn.

• You need to create various measures that help you perform several calculations and carry out the required analysis.

These steps essentially constitute the data modeling steps for Profit and Loss Analysis using the data model. However, this is the sequence of steps for any type of data analysis that you want to perform with Power Pivot data model.

Further, you will learn how to create the measures and how to use them in the Power PivotTables in the subsequent chapters. This will give you sufficient understanding of data modeling with DAX and data analysis with Power PivotTables.

Adding a Date Table to the Data Model

Create a Date table for the time periods spanning the fiscal years as follows −

• Create a table with a single column with header – Date and contiguous dates ranging from 7/1/2011 to 6/30/2018 in a new Excel worksheet.

• Copy the table from Excel and paste it into the Power Pivot window. This will create a new table in the Power Pivot data model.

• Name the table as Date.

• Ensure that the Date column in the Date table is of data type - Date (DateTime).

Next, you need to add the calculated columns – Fiscal Year, Fiscal Quarter, Fiscal Month and Month to the Date table as follows −

Fiscal Year

Suppose the fiscal year end is June 30^th. Then, a fiscal year spans from 1^st July to 30^th June. For example, the period July 1^st, 2011 (7/1/2011) to June 30^th, 2012 (6/30/2012) will be the fiscal year 2012.

In the Date table, suppose you want to represent the same as FY2012.

• You need to first extract the financial year part of the Date and append it with FY.

  • For the dates in the months July 2011 to December 2011, the financial year is 1+2011.

  • For the dates in the months January 2012 to June 2012, the financial year is 0+2012.

  • To generalize, if the Month of Financial Year End is FYE, do the following −

    Integer Part of ((Month – 1)/FYE) + Year

  • Next, take the rightmost 4 characters to obtain the Financial Year.

• In DAX, you can represent the same as −

  RIGHT(INT((MONTH('Date'[Date])-1)/'Date'[FYE])+YEAR('Date'[Date]),4)

• Add the calculated column Fiscal Year in the Date table with the DAX formula −

  ="FY"&RIGHT(INT((MONTH('Date'[Date])-1)/'Date'[FYE])+YEAR('Date'[Date]),4)

Fiscal Quarter

If FYE represents the month of financial year end, the financial quarter is obtained as

Integer Part of ((Remainder of ((Month+FYE-1)/12) + 3)/3)

• In DAX, you can represent the same as −

  INT((MOD(MONTH('Date'[Date])+'Date'[FYE]-1,12)+3)/3)

• Add the calculated column Fiscal Quarter in the Date table with the DAX formula −

  ='Date'[FiscalYear]&"-Q"&FORMAT( INT((MOD(MONTH('Date'[Date]) + 'Date'[FYE]-1,12) + 3)/3), "0")

Fiscal Month

If FYE represents the financial year end, the financial month period is obtained as

(Remainder of (Month+FYE-1)/12) + 1

• In DAX, you can represent the same as −

  MOD(MONTH('Date'[Date])+'Date'[FYE]-1,12)+1

• Add the calculated column Fiscal Month in the Date table with the DAX formula −

  ='Date'[Fiscal Year]&"-P" & FORMAT(MOD(MONTH([Date])+[FYE]-1,12)+1,"00")

Month

Finally, add the calculated column Month that represents the month number in a financial year as follows −

=FORMAT(MOD(MONTH([Date])+[FYE]-1,12)+1,"00") & "-" & FORMAT([Date],"mmm")

The resulting Date table looks like the following screenshot.

Mark the table – Date as Date Table with the column - Date as the column with unique values as shown in the following screenshot.

Adding Calculated Columns

To create a relationship between the Finance Data table and the Date table, you require a column of Date values in the Finance Data table.

• Add a calculated column Date in the Finance Data table with the DAX formula −

  = DATEVALUE ('Finance Data'[Fiscal Month])

Defining Relationships Between Tables in the Data Model

You have the following tables in the data model −

• Data table - Finance Data
• Lookup tables - Accounts and Geography Locn
• Date table - Date

To define Relationships between the tables in the data model, following are the steps −

• View the tables in the Diagram View of the Power Pivot.

• Create the following relationships between the tables −

  • Relationship between Finance Data table and Accounts table with the column Account.

  • Relationship between Finance Data table and Geography Locn table with the column Profit Center.

  • Relationship between Finance Data table and Date table with the column Date.

Hiding Columns from Client Tools

If there are any columns in a data table that you won’t be using as fields in any PivotTable, you can hide them in the data model. Then, they will not be visible in the PivotTable Fields list.

In the Finance Data table, you have 4 columns – Fiscal Month, Date, Account and Profit Center that you won’t be using as fields in any PivotTable. Hence, you can hide them so that they do not appear in the PivotTable Fields list.

• Select the columns - Fiscal Month, Date, Account, and Profit Center in the Finance Data table.

• Right-click and select Hide from Client Tools in the dropdown list.

Creating Measures in the Tables

You are all set for data modeling and analysis with DAX using the data model and Power PivotTables.

In the subsequent chapters, you will learn how to create measures and how to use them in Power PivotTables. You will create all the measures in the data table, i.e. Finance Data table.

You will create measures using DAX formulas in the data table – Finance Data, which you can use in any number of PivotTables for the data analysis. The measures are essentially the metadata. Creating measures in the data table is part of data modeling and summarizing them in the Power PivotTables is part of data analysis.