Challenge level: 3 out of 5
Estimated reading time: 30 min
Power Pivot functions covered: SUM(), CALCULATE(), SAMEPERIODLASTYEAR(), DATESYTD(), DATESINPERIOD(), LASTDATE() and DISTINCTCOUNT()
Excel attachment reference sheets: template_Power Series
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 the “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.
1. Create relationships between 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.
1.1. First, click on the “Diagram View” button on the “Home” tab.
1.2. 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.
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 a formula (we'll get to this).
2.1. Write your first measure
A measure is like a formula 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.
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".
In case you were curious about how to get the slicers on the left side of the table:
2.2. Get previous years’, YOY, YTD and FYTD figures in seconds
Though the first measure was very simple, you'll find its usefulness in the upcoming subsequent measures.
2.2.1. 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() 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?
This measure also features our first cross-reference of different tables within the same formula. Both the Data table (used in the [total] measure) and Calendar table are referenced here.
2.2.2. 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.
The same method is to be applied for subsequent YOY calculations of YTD and FYTD figures too.
2.2.3. YTD figures
The next significant formula exclusive to Power Pivot to highlight is the DATESYTD() formula. The 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?
To get YTD figures for the same period last year, simply replace the [total] measure with [total_YTD] and use the SAMEPERIODLASTYEAR() formula.
2.2.4. FYTD figures
This portion is only important if your company’s financial year does not follow the standard calendar year. The 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 month accordingly. The year doesn’t matter).
2.3. Tackle L12M & L12M_Average calculations easily
Like all the other Power Pivot formulas we have covered so far, the formulas for L12M and L12_Avg are intuitive as well.
2.3.1. Equation for L12M figures
Take note of these Power Pivot functions: DATESINPERIOD() and LASTDATE(). Combining these two 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() 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.
2.3.2. Equation for L12M_Avg figures
Just like in day 2, we've introduced Power Pivot’s DISTINCTCOUNT() 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 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).
Did we manage to get you excited about Power Query and Power Pivot? If you are interested in a more in-depth approach to Power Query and Power Pivot, check us out here!