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

14 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.

  3. Great post, thank you! How can I modify this to handle months that have no values? In Excel, it would return #DIV/0! and in PowerBI, it calculates to -100% or ‘Infinity’. Can you suggest a way for it to return “NA” is something similar?

    Thank you

    • In order to do this you would want to use an if statement to catch when the value would be zero. The measure would look like the following:

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

      The if statement determines if the division resolves in a zero. Another way to write this to handle the infinity condition as well would be to do the following:

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

      in this example we are testing the measures “ThisYear” and “LastYear” and if either value is zero then we return a 0 for a number. If you’d like to remove the number entirely from the calculation you use a null instead of a zero. See below for example:

      YoY Percent Change = if(or([ThisYear] = 0, [LastYear] = 0), "null", DIVIDE([ThisYear], [LastYear], 0)-1)

      Hope that helps.

  4. Dears,

    Great post.
    It helped me a lot for the reports i’m making.

    I have a question though.
    Can you assist me in creating a measure which returns total sales for current week and or month?

    I tried using your example and replaced Year with Month but it doesn’t work.
    It says: DAX comparison operations do not support comparing values of type Text with values of type Integer.

    Given the fact that im new to PowerBI i couldn’t wrap my head around it to transform my Date(month) to integer.

    Can you assist me please?

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

    • To calculate a Month to date you would use something similar to the following
      Month To Date = TOTALMTD( SUM( ‘TableName'[ColumnName] ) , ‘TableName'[Date].[Date] )

      a Week to date is more complex.
      First you would want to make a new column in the query editor and use the following code:
      = Table.AddColumn(#”Renamed Columns”, “WeekOfYear”, each Date.WeekOfYear([Date]), type number)
      Note: this can be automatically generated by clicking the Add Column ribbon in the Query Editor, highlight the Date column (make sure this data type is a date), Clicking the Date drop down, and selecting Week, then Week of Year.
      Close and apply to load the data from the query editor.
      Then you can make a table visual where you include the sales column and the week of the year column. Set the week of the year column to not summarize, and set the Sales to sum, and you will have the total number of sales by week.

  5. Your post really helps.
    I followed your steps and created the Last year sales, this years sales, YoY%. But when I put YoY% in the visual, it can’t display the visual. The reason it says:
    Calculation error in measure Deal[This Year Sales]: DAX comparison operations do not support comparing values of type Text with values of type Integer.

    How am I gonna do to fix this?

    • This is a simple fix. You will need to make sure that all your calculations using the SUM are of a specific data type. The data type should be an integer or number. To fix this click on all the column that is being summed which should be sales. Click on the Modeling ribbon, and then change the Data Type: to either Whole Number or Decimal Number. That should force the column to be the correct data type for the sum calculation to work.

  6. can we do this in the cloud version? I’ve downloaded the Google Analytics Content pack and would like to add YoY comparisons from here. Is that possible? Thanks.

  7. Hi Mike,
    Great info! Thanks!
    However, your example shows Calendar YoY. What would be possible solution when you need to show YoY for fiscal year that involves two calendar years, like when FY starts in June 2015 and ends May 2016. Could you please advise on this scenario?

    Regards

Leave a Reply

Your email address will not be published.


*


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