Year Over Year Sales by Month

Year-over-year (YoY) data viewed by month can give you insights about how a business is doing, while taking time periods into account. This tutorial uses the sample PLUGS data to show year-over-year sales by [Month of Date].

During this tutorial, you can learn more about how to use groupings and calculations within groupings. 

The image below shows the table with the [YoY by Month] column listing the percentage change. The values are calculated using window functions, which means that calculations are affected by how the table is grouped and sorted. For more information see Window Function Overview.

Contents

Tutorial Steps

1 - Create a Sales Column

2 - Group by Month

3 - Group by Year

4 - Calculate Monthly Sales

5 - Calculate the Year over Year Data for the Month

Use a Child Table for Further Explorations

Tutorial Steps

1 - Create a Sales Column

Your data may already have a Sales column. The sample PLUGS data does not, so for this tutorial you can create a new column called Sales.

1. Create a new column, either:
   • From a column's menu, select Add new column.
   • From the left panel Columns list, select + Add new column.
2. Enter the formula in the formula bar:

   [Quantity] * [Price]

3. Name the new column [Sales].
4. From the column's menu, change the format to Currency.
   Your new column looks like this:
   

2 - Group by Month

1. Use the [Date] column and the DatePart function to isolate the month. Create a new column and use the formula:

   DatePart("month", [Date]) 

   Sigma displays a new column with the month represented by the numbers 1 through 12, and automatically names it [Month of Date].
2. Create a new grouping defined by [Month of Date]. Either:
   • In the left panel, drag the [Month of Date] column up to the Groupings section, or
   • Click the + Add Grouping menu next to the Groupings heading.
3. Sort the [Month of Date] column by ascending order, so that month 1 (January) is on top.
4. Collapse the months using the +/- button in the column header. 
   Your table looks like this:
   
   
   

3 - Group by Year

1. Again, use the [Date] column, and this time use the DatePart function to isolate the year. Create a new column and use the formula:

   DatePart("year", [Date])
   

   Sigma displays a new column that aggregates the years by month and automatically names the column [Year of Date].
2. Create a new grouping defined by [Year of Date]. Either:
   • In the left panel, drag the [Year of Date] column up to the Groupings section, or
   • Use the + Add Grouping menu next to the Groupings heading.
     Tip: Make sure that this is a new, separate grouping from the Month of Date grouping.
3. Use the +/- button in the column header to collapse the [Year of Date].
4. Sort [Year of Date] by descending order, so that the most recent year is on top.
   Your table looks like this:
   

   

4 - Calculate Monthly Sales

1. In the left panel Grouping section, use the + Add calculations menu to add a new aggregate column to the [Year of Date] grouping. (This is a bit non-intuitive, you do not add it to the [Month of Date] grouping.) 
   

   
   Select the [Sales] column.

   Sigma automatically applies the aggregate Sum. The new column, named [Sum of Sales], shows all of the sales that happened in a specific month in that specific year.
2. Rename the column "Monthly Sales".
   Your table looks like this:
   
   
   

5 - Calculate the Year over Year Data for the Month

1. Create a new calculations column in the [Year of Date] grouping. Select + Add new column. 
   Sigma adds a new column to which you can apply a formula.  
2. Use the Lead function to get the previous month's sales. Use the formula:

    Lead([Monthly Sales])

3. Rename the column "Previous Month".
   
   
4. Create another new column in the [Year of Date] grouping. 
5. Use the new [Previous Month] column to calculate the percent change. Use the formula:

    ([Monthly Sales] - [Previous Month]) / [Previous Month]

6. Rename the column to [YoY by Month]. 
7. Change the column format to percent.
   
   Your table now looks like the one at the top of this page.

Use a Child Table for Further Explorations

To see YoY values in a different configuration, create a child element using this table as a source. Creating a child table turns all of the calculated values into static values, allowing you to rearrange the new table without interrupting the window functions.