Sign in or start a free trial to avail of this feature.
4. Making Comparisons Over Time
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.
Comparisons to Previous Time Periods
- We often want to study how a field such as revenue grows or contracts over time
- We can identify a previous time period using the function PARALLELPERIOD
- We can then easily measure the change in revenue from this previous period
- We calculate the percentage change in revenue by dividing the change in revenue by the previous revenue amount
- Alt N, V – Create PivotTable
- Alt B, F – Create new measure
- Alt N, SZ, C – Create PivotChart
- Alt JC, C – Change chart type
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.