[5/5] Power Pivot's Data Model and DAX formula in a nutshell (The Power Series Way, Part 2)


  • Challenge level: 3 out of 5

  • Estimated reading time: 30 min

  • Power Pivot DAX formulas covered: SUM(), CALCULATE(), SAMEPERIODLASTYEAR(), DATESYTD(), DATESINPERIOD(), LASTDATE() and DISTINCTCOUNT()   

  • Excel attachment reference sheets: template_Power Series


BEFORE YOU START: Get your FREE Excel template that accompanies this five-part tutorial by subscribing here!


Power Pivot takes over

Now that we are done setting up the queries, it is finally time to address the “mythical” Data Model that we have been adding the queries to. On Excel’s “Data” tab and under the “Data Tools” section, there is a “Manage Data Model” button (Or alternatively on the “Power Pivot” tab, there is the “Manage” button under the “Data Model” section). Click on it.

A separate window will load. This is the data model that we have been importing all the different data tables we created through the queries to. This data model is actually the operating platform of Power Pivot.

 Power Pivot Data Model data view window in Excel

 

Step 1. Create relationships between data tables

At the moment, the three data tables we created under Power Query are separate even though they contain common data. In order to link them up, we need to create relationships between them. The easiest way to do this is by drag-and-drop.

Step 1.1. Activate Power Pivot’s Diagram View

First, click on the “Diagram View” button on the “Home” tab.

 Power Pivot Data Model diagram view in Excel

Step 1.2. Link data tables together

We want to link up Category table to Data table. The data that both tables have in common is “category”. Hence, click on “category” in the Category table , drag your mouse towards Data table and then release mouse click on the “category” word in Data table. A connecting line joining the two tables will appear making the two tables now relate. Repeat the same for Calendar and Data tables, noting that “date” is the common data between both tables.

It is important to make sure that the arrows between the tables point in the right direction. i.e. from One to Many. One: Table only has one unique record e.g. date in Calendar. Many: Table can have multiple same records e.g. date in Data where could be multiple transactions on the same date.

 Power Pivot Data Model diagram view drag-and-drop to relate tables
 Power Pivot diagram view link data tables together

 

Step 2. Write simple yet powerful formulas

Why is it important for the tables to be related? It allows the Power Pivot platform to pull the relevant data across the different tables, when cross-referencing these tables within DAX formulas (we'll get to this).  

 

Step 2.1. Write your first measure with DAX formulas

A measure is like a function made up of one or more DAX formulas that is both time-saving and error-minimising. Better yet, you can save and re-use it. This is a function distinctive to Power Pivot.

The first measure in our template is one that sums up the “value” column from our primary Data table. There are various ways to create a measure. The easiest, is to go back to “Data View”, select a cell in the second half of the window (after the data table) and write the measure in the formula bar.

 Power Pivot Data Model data view DAX formula SUM
 Power Pivot how to create total measure using DAX formula SUM

To see all the different permutations of values created from this simple measure, click on the "Pivot Table" button under the Home tab. Note that any pivot table created, will sit on a normal Excel worksheet. Even if you've been working on the Power Pivot data model platform so far. In this case, our pivot table sits on the sheet titled "template_Power Series". 

 Using slicers with Power Pivot tables to see different permutations of values created from one measure

In case you were curious about how to get the slicers on the left side of the table:  

 how to add slicers to the Power Pivot table

 

Step 2.2. Get previous years', year-on-year (YOY), year-to-date (YTD), and financial-year-to-date (FYTD) figures in seconds with DAX formulas

Though the first measure was very simple, you'll find its usefulness in the upcoming subsequent measures.

Step 2.2.1. Get previous years’ figures

The second measure in the template returns previous years’ “value” figures. This means the measure automatically identifies, which figures from the “value” column falls into which year (and month), and which year is considered the previous year when viewed in relation to a different year.

The CALCULATE() and SAMEPERIODLASTYEAR() DAX formulas are used here and just like measures, they are exclusive to Power Pivot. Simple yet intuitive, they are also incredibly powerful. It's a relief when you think about our recent struggles! Remember wrestling with writing smart formulas, to factor in different time period changes in normal Excel calculations?

 Power Pivot create total last year measure using DAX formula CALCULATE and SAMEPERIODLASTYEAR

This measure also features our first cross-reference of different tables within the same DAX formula. Both the Data table (used in the [total] measure) and Calendar table are referenced here.

 Power Pivot DAX can cross-reference different tables. Makes use of calendar table for time-intelligent functions.

Step 2.2.2. Get YOY figures

With both [total] and [total_last year] measures, we can easily get YOY figures by using the same simple mathematical equation that we used for the normal Excel way.

 Power Pivot create total year-on-year measure using DAX formula DIVIDE

The same method is to be applied for subsequent YOY calculations of YTD and FYTD figures too.

 Using slicers with Power Pivot table Year-on-year measure

Step 2.2.3. Get YTD figures

The next significant DAX formula exclusive to Power Pivot to highlight is the DATESYTD() formula. The DAX formula is pretty much self-explanatory. It is able to automatically identify and sum up figures from the “value” column in Data table which falls into the past but still within the same year when compared against a different date reference.  

Pretty intuitive, right?ea

 Power Pivot create total year-to-date measure using DAX formula CALCULATE and DATESYTD
 Power Pivot table check year-to-date measure is correct

To get YTD figures for the same period last year, simply replace the [total] measure with [total_YTD] and use the SAMEPERIODLASTYEAR() DAX formula.

 Power Pivot create total year-to-date last year measure using DAX formula CALCULATE and SAMEPERIODLASTYEAR
 Power Pivot table with measure year-to-date (YTD) last year

Step 2.2.4. Get FYTD figures  

This portion is only important if your company’s financial year does not follow the standard calendar year. The DAX formula for FYTD figures is pretty similar to the YTD one, with one additional detail: you will need to specify the year-end date. The example used in our template assumes that the company’s financial year ends in March. Since our data only runs until 2009, our year-end date is 31/03/2009. You would need to modify this manually based on your data set (i.e. change the date and month accordingly. The year doesn’t matter).

 Power Pivot create total financial-year-to-date measure using DAX formula CALCULATE and DATESYTD
 Power Pivot table financial-year-to-date (FYTD) measure easily included

 

Step 2.3. Tackle Last-12-Months (L12M) and L12M Average calculations easily

Like all the other Power Pivot DAX formulas we have covered so far, the DAX formulas for L12M and L12_Avg are intuitive as well.

Step 2.3.1. Write equation for L12M figures

Take note of these Power Pivot DAX functions: DATESINPERIOD() and LASTDATE(). Combining these two DAX functions allows us to build a dynamic formula. The formula updates itself to return a table of specific values, that are summed up figures of the last 12 months for every different time period.

DATESINPERIOD enables you to define the specific time length you want to narrow down your returned values to. In our case, we want a specific time frame of the past 12 months. Change the Number of Intervals field to -6 if you want the past 6 months instead or to 6 if you want figures of the next 6 months. If you want the calculated intervals to be years instead of months, amend the Interval field to reflect Year. A breeze, right?

By using the LASTDATE() DAX function as a date anchor, the formula automatically returns new summed up last 12-month figures- if the updated raw data added contains dates that fall outside the current time periods.    

 Power Pivot create total last-12-months measure using DAX formula CALCULATE, DATESINPERIOD and LASTDATE
 Power Pivot table check that SUM is correct

Step 2.3.2. Write equation for L12M_Avg figures

Just like in day 2, we've introduced Power Pivot’s DISTINCTCOUNT() DAX function here, as we have to account for situations where there's less than 12 months of existing data; to sum up and divide accordingly by the correct number of months. This is so an accurate average figure is reflected. The concept of DISTINCTCOUNT() is pretty similar to Excel’s MATCH(), in that it serves to return a count value.

When used together with CALCULATE() here, we're able to narrow down the data range where the count should be carried out on. Based on our formula set-up below, we have instructed the DISTINCTCOUNT() function to return the exact number of months where data exists within the last 12 months, for every different time period. In most instances, it will return the count of 12 as defined by the DATESINPERIOD() function. The exceptions would be when dealing with the very first 11 months of the raw data set (i.e. when there are less than 12 past months to sum up).    

 Power Pivot create total last-12-month average using DAX formula DIVIDE, CALCULATE, DISTINCTCOUNT, DATESINPERIOD, LASTDATE
 Power Pivot table check that last 12 month average measure is correct

Did we manage to get you excited about Power Query and Power Pivot? If you are interested in a more in-depth approach, check out our Power Query and Power Pivot training courses