Growth percentages from previous days

Overview

This article explains how to add a column that shows the percentage growth in sales from the previous day

Steps

1. Create a previous date column

  • Click inside the table in the worksheet, then click the From Table/Range icon on the Data tab in Excel to create a new query. The Query Editor window will open.

Since this data comes from an Excel table, you have to explicitly set the type of the Date column.

  • Select the Date column in the Query Editor, and in the Data Type drop-down menu in the Query Editor toolbar, select Date.

Next, create a custom column that holds the date that is the day before the date in the Date column.

  • Click the Custom Column icon on the Add Column tab in the Query Editor toolbar and the Custom Column dialog will appear.

  • Enter the text PreviousDate in the New column name box, and in the Custom column formula box enter the following M expression to subtract a duration value of one day from the date in the Date column:

M-Code 
    = [Date] - #duration(1,0,0,0)

The Custom Column dialog should be as shown below. Click OK to close the dialog.

2. Merge the query with itself

At this point you should have a new column in your original table, as shown below:

The next step is to look up the sales associated with the previous day’s date and add those to the table. You can do that by merging the table with itself, joining the PreviousDate column to the Date column.

  • Click the Merge Queries button on the Home tab in the Query Editor toolbar and the Merge Queries dialog will appear with the current table selected in the top half.
  • In the drop-down box to select the table to merge with, select the current query again; its name should be something like PercentageGrowthPrevDayInput (Current).
  • In the top table select the PreviousDate column; in the bottom table select the Date column.

The dialog should be as shown below. Click OK to close the dialog.

Once you have closed the Merge dialog, the Query Editor will show a new column in the table containing table values, as shown below:

3. Create a calculated column

The next thing to do is to expand the column called AddedCustom and return the Sales values from it.

  • Click the expand icon in the column header for AddedCustom and uncheck everything except the Sales column, as shown below. Then click OK.

The Query Editor will now show the sales for the date shown in the PreviousDate column in a new column called Sales.1. Rename this column by double-clicking the column header and enter PreviousDateSales to be the new column name. The result should be as shown below:

You should now have two columns that contain the values you need to calculate the percentage growth value.

  • Click the Custom Column button on the Insert tab in the Query Editor toolbar once again.
  • In the Custom Column dialog, name your new column PercentageSalesGrowth and enter the following formula:
M-Code 
    = ([Sales] - [PreviousDateSales]) / [PreviousDateSales]

The dialog will be as shown below. Click OK to close.

You should now have the raw percentage sales growth values in a column in your table.

  • Now select the PreviousDate and PreviousDateSales columns, right-click them, and click Remove Columns to remove them from the table.
  • The table will also no longer be in date order, so select the Date column and click the Sort Ascending button on the Home tab in the Query Editor toolbar to sort the table by date.

The table will be as shown below:

  • Click the Close & Load button to close the Query Editor
  • Finally, in the output table in the Excel worksheet, you can format the values in the PercentageSalesGrowth column as percentages by selecting the column in the worksheet and selecting Format Cells from the right-click menu.

The table will then be as shown below.

4. The ‘M’ code

M-Code 

let
    //Load data from Excel workbook
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    //Set the type of the Date column to be date
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales", Int64.Type}}),
    //Calculate the date one day before the current date
    #"Added Custom" = Table.AddColumn(#"Changed Type", "PreviousDate", each [Date] - #duration(1,0,0,0,
    //Join the table to itself to look up the previous date value
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"PreviousDate"}, #"Added Custom", {"Date"}, "Added Custom", JoinKind.LeftOuter),
    //Expand the Sales column from the joined table
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Sales"}, {"Sales.1"}),
    //Rename the new column to PreviousDateSales
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Added Custom",{{"Sales.1", "PreviousDateSales",
    //Calculate the percentage growth
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "PercentageSalesGrowth ", each ([Sales] - [PreviousDateSales]) / [PreviousDateSales]),
    //Remove the PreviousDate and PreviousDateSales columns
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"PreviousDate", "PreviousDateSales"}),
    //Sort the table in ascending order by the Date column
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"