Divide – PowerBI.tips

This tutorial is a variation on the month to month percent change tutorial. This specific exploration in year over year performance was born out of reviewing my google analytics information. The specific analysis question I am trying to answer is, how did this current month of website visitors compare to the same month last year. For example I want to compare the number of visitors for November 2016 to November 2015. Did I have more users this year in this month or last year? What was my percent changed between the two months?

Here is a sample of the analysis:

let’s begin with loading our data and data transformations. Open up PowerBI Desktop, Click the Get Data button on the Home ribbon and select Blank Query. Click Connect to open the Query Editor. Click Advanced Editor on the View ribbon. While in the Advanced Editor paste the following code into the editor window, click Done to complete the data load.

Note: If you need some more help loading the data follow this tutorial about loading data using the Advanced Query Editor. This tutorial teaches you how to copy and paste M code into the Advanced Editor.

let
 Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdDBDcQwCETRXnyOFMAYcC1W+m9jV8BhfH1ygJ9zBr/8CvEaz+DYNL7nDAFjnWkTTNsUbIqnLfyWa56BOXOagy2xtMB5Vjs2mPFOYwIkikIsWd6IKb7qxH5o+bBNwIwIk622OCanTd2YXPNUMNnqFwomp0XvDTAPw+Q2uZL7QL+SC1Wv5Dpx/lO+Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Start of Month" = _t, Sales = _t]),
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start of Month", type date}, {"Sales", Int64.Type}}),
 #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Start of Month]), type number),
 #"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([Start of Month]), type number)
in
 #"Inserted Year"

While still in the Query Editor rename the query to Data. Then click Close & Apply to complete the data load into the data model.

Next, make four measures. On the Home ribbon click the New Measure button. Enter the following to establish a reference date to the subsequent equations:

Date Reference = DATE(2016,12,31)

Enter in the following equation to calculate the last year monthly sales amount.

LastYear = 
  VAR 
    CurrentDate = [Date Reference]
  RETURN
    CALCULATE( 
     SUM(Data[Sales]), 
     Data[Year] = YEAR(CurrentDate)-1
    )

Note: Using the NOW() function calls the current time when the query was last run. Thus, if you refresh your data next month the NOW() function wrapped in a YEAR() will return the current year from the date-time observed by PowerBI.

Following the same process enter the following additional measures. The ThisYear measure calculates the sales for the current month.

ThisYear = 
  VAR 
   CurrentDate = [Date Reference] 
  RETURN
   CALCULATE(SUM(Data[Sales]),Data[Year] = YEAR(CurrentDate))

Finally, add the calculation for the Year to Year comparison.

YoY Percent Change = DIVIDE([ThisYear], [LastYear], 0)-1

Since the YoY Percent Change is a real percentage we need to change the formatting to a percent. Click on the YoY Percent Change measure then on the Modeling ribbon click the % symbol in the formatting section of the ribbon.

Next, add a Stacked Column Chart with the following columns selected.

OK, we have a chart, but it is kinda awkward looking right now. The x-axis is the month number but we don’t have a month 0. That simply does not make sense. Let’s change some of the chart properties. While having the Stacked Column Chart selected click on the Paint Roller in the Visualizations pane. First, click on the X-Axis and change the Type to Categorical.

Then click on the Data Colors and turn on Diverging. Change the Minimum color to Red and the Maximum color to Green. Set the Center to a value of 0.

Click on the Title change it something meaningful, Center the text and increase the font size.

Our bar chart looks much better. However, the month numbers do not look quite right. Visually the month indicators would be cleaner if we didn’t have any decimals. Click on the Month field and then on the Modeling ribbon change the Data Type to Whole Number. There will be a warning letting you know that you are changing the Data Type of the Whole number. Click OK to proceed with the change.

Another successful percent change tutorial completed. I hope you enjoyed this year over year month comparison example. Make sure you share if you like what you see.

PowerBI.tips

Tag: Divide

Measures – Year Over Year Percent Change