4. Making Comparisons Over Time

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

Visualizing data over time is useful, but sometimes you’ll prefer to see numbers. In this lesson, we’ll learn how to find the difference in revenue between different time periods.

Summary

  1. Lesson Goal (00:19)

    The goal of this lesson is to create measures that help us analyze and compare revenue changes over time.

  2. Calculating the Previous Month’s Revenue (00:26)

    There are many useful metrics that can help us evaluate changes in revenue over time. In our lesson, we start with a Pivot Table of revenue by month and we want to calculate revenue for the previous month. This will help us track revenue trends.

    Revenue for a previous time period can be calculated using a DAX measure. The measure uses CALCULATE to calculate the sum of revenue. The filter condition uses the PARALLELPERIOD function to find the previous month. PARALLELPERIOD takes three arguments: first is the date column of interest, second is the number of intervals to shift by, and third is the type of interval. To identify dates from the previous month, we use -1 as the number of intervals and months as the type of intervals. After creating this measure, we can add it to a Pivot Table to see revenue for the previous month.

  3. Calculating the Revenue Change (02:03)

    After calculating revenue for the previous month, we can use the measure to calculate the revenue change from month to month. We can create a DAX measure to calculate this revenue change. The measure subtracts the previous revenue measure from the sum of the sales column. When we add this measure to the Pivot Table, it calculates the change in revenue from the previous month for each month in the data set. This helps us to see if a variable is generally rising or falling over time.

  4. Calculating the Percentage Revenue Change (03:04)

    When we calculate absolute changes in the value of a variable, it can be difficult to understand the relative magnitude of these changes. We can deal with this by calculating the percentage change in revenue from one month to another. This measure can be created easily using the previous two measures. We use DIVIDE to divide the change in revenue by the previous month’s revenue. This shows us the change in revenue each month as a percentage, which can make it easier to understand the relative magnitude of the revenue change for each month.

  5. Creating a Pivot Chart (04:05)

    After creating all these variables, a Pivot Chart can be used to visualize the revenue trends over time. In our case, we create a line chart showing the monthly percentage change in revenue over time. The last month of data for this variable has a value of -100%, so we add a filter to the chart removing this value. Studying these variable visually can provide relevant insights. For example, we can see that revenue fell in each of the final three months of our data set, although the rate of decline fell in that time. This trend would be concerning and this analysis could spark an investigation of what has caused it.

Transcript

When you have a data set with a date dimension, you'll almost always wanna analyze it over time. In Power Pivot, it's easy to create charts that let you visualize the overall trends, but it can be slightly more difficult to identify the numbers behind these visuals.

In this lesson we'll create measures that help us analyze and compare changes in revenue over time.

We'll start by creating a table that displays revenue by month. We'll create a pivot table, and put it on a new sheet.

We'll then navigate to the dates table, add the date hierarchy, navigate to the sales table, add the revenue field, and expand the table down to the monthly level.

We're now ready to use decks to create measures that will give us the numbers behind our revenue changes over time.

First, we'll create a measure that returns the sales figures from the previous month.

We'll create a new measure, call it Previous Revenue, and enter a CALCULATE function.

The expression to calculate will be the sum of revenue.

The filter condition will use the function PARALELLPERIOD.

This shifts the dataset by a specified number of periods.

We want to shift the date field back one month.

The date argument is therefore the date column from the dates table, the number of intervals is negative one, and the type of interval is MONTH.

We'll now close the formula, set the category as Currency, the symbol as US dollar sign, and select OK to create the measure.

We can see that it correctly computes the sales for the previous month.

Next, we'll create a measure calculating the change in revenue from month to month.

We'll create a new measure, and call it Revenue Change.

For any specific month, the change in revenue will simply be the total revenue for that month minus the revenue from the previous month. The formula is therefore the sum of revenue minus the Previous Revenue measure we just created.

We'll apply the same number formatting as before, and select OK to create it.

This measure records the increase or decrease in revenue for each month in the dataset.

Calculating differences between fields is commonly used to analyze whether a field of interest is consistently rising or falling over time.

This Revenue Change measure is not bad, but it's somewhat difficult to compare the magnitude of revenue changes in different months.

We'll add a measure that computes the change in revenue from month to month in percentage terms.

Once again, we'll create a new measure, and this time, call it Revenue % Change.

To find the percentage change in revenue, we divide the change in revenue by the previous month's revenue figure.

Therefore, we'll use the DIVIDE function.

The numerator will be the Revenue Change measure, and the denominator will be the Previous Revenue measure.

We'll close the formula, set the category to Number, the format as Percentage, and select OK to create the measure.

This expresses the Revenue Change we created previously as a percentage.

Let's create a line chart to see a visual representation of the Revenue % Change measure.

We'll select any cell outside the pivot table, and then insert a pivot chart with Alt, N, S + Z, C.

We'll navigate to the dates table, add the date hierarchy and expand to month, navigate to the sales table, and add the Revenue % Change measure.

We'll then change the chart type, and select a line chart.

Finally, we'll go to the dates table and drag Year to the filter area.

We'll filter the chart by year to show us only 2016 data.

This removes the negative 100% observation that comes at the end of the dataset.

We can see that the percentage change is volatile, which we would expect since, in the previous lesson, we saw that the trend of revenue is volatile.

What is notable, is that the last three months are all below zero, implying prolonged decline in revenue, however, the percentage decline is reducing each month, suggesting the decline is not getting worse every month.

Nonetheless, the company should be concerned about this trend.

Note that these measures are slightly inflexible, and only track month on month changes in revenue.

Depending on the specific business case, you may want to see two month changes or three month changes instead.

In the next lesson, we'll give users greater control over this analysis by adding a slicer that lets users customize the offset length.

Excel Excel for Business Analytics Learning Plan
DAX in Power Pivot
Practical Applications of DAX

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial