Measures – Year Over Year Percent Change

Year to Year Percent Change
Year to Year Percent Change

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.

Load Monthly Data
Load Monthly Data

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.

Change Measure Format
Change Measure Format

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

Add Stacked Column Chart
Add Stacked Column Chart

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.

Change X-Axis
Change X-Axis

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.

Change Colors
Change Colors

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

Change Title
Change Title

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.

Change Month to Whole Number
Change Month to Whole Number

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.

It's only fair to share...
Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn14Pin on Pinterest0Share on Tumblr0

4 Comments

  1. Hello,

    I’m totally new to power BI. I’ve created small spreadsheet and connect it to power BI. In the spreadsheet, it has few columns, i.e Item A, B, and C, and the number of item of these items are updated everyday. It seems that when someone making change to number of item in the sheet data source, the power BI would only display the last data available. How can i keep power bi to keep the previous data, so that it displays in a incremental form ?

    • Tony, great question. PowerBI will only be able to display what is currently listed in the file. Thus, if you have items A, B, C, from yesterday and now today that data has been modified to only have A, & B. Then Power Bi upon refresh will only show A & B items. In order to capture historical data you would need to have multiple files with the same format but with different names, such as 2016-12-15 Data, and 2016-12-16 Data. Then using power BI compile all the differently daily files into one data model. For compiling multiple excel files into one data model you can follow along with this tutorial.

      In summary, to capture all your historical data you will need a process in your excel file or files that allows you to keep all that data instead of changing the data. Hope that helps, thanks for the question.

  2. Is it possible to do this same thing but to use weeks instead of months? All of the data that I use in PowerBI is weekly grain and uses Saturday as the week ending day. For example,

    week 1 = 2/18/2017
    week 2 = 2/25/2017
    week 3 = 3/4/2017
    etc.

    • I think this is fairly easy to implement. To do this you could add another column that has the week number of the year. To add this column you would have to open up the query editor click on the Add Column ribbon and then click the Date button. Select Week then select Week of Year. This will provide a column that increments the week of the year from January 1 (week 1) through December (Week 52). Your new column should be automatically labeled WeekOfYear. Close the Query Editor and then change your two measures to use the new column WeekOfYear instead of the Year column. This should now allow you to calculate year over year week percent changes.

Leave a Reply

Your email address will not be published.


*


Comment moderation is enabled. Your comment may take some time to appear.