Measures – Year Over Year Percent Change

2016-12-05 Mike Carlo Report Data Tips
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.

Related Articles

29 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

  8. These tutorials are absolutely fantastic. But I wonder why in this case you are not using SAMEPERIODLASTYEAR function?

  9. I am new to Power BI and I am trying to recreate a chart I use in Excel that shows the following:
    Primary Axis: TY $ Sales as a line, LY $ Sales as a bar
    Secondary Axis: $ % Chg Ty vs LY as a line
    Is there a way to create this? The ‘line and stacked column chart’ and the ‘line and clustered column chart’ options do not display the data properly.
    Also, is there a way to show labels on the % Chg only?
    Thank you for any assistance you can provide!

    • I don’t believe there is a visual at this time that allows such flexibility with your y-Axis. When you use the line and bar chart, the line has an axis and the bar will have a secondary axis. I don’t believe you are able to have two secondary axis’ for bars on the same chart.
      What I have done with mild success is to construct the chart this way:
      1. Use the Bar and Line chart.
      2. This year sales & Last year sales as lines.
      3. Use the bars as percent change.

      When I presented this chart to business leaders they liked the style and intuitive nature of the chart. Best of luck, Mike.

  10. Is there a way to get the month name rather than just an awkward number to show, and have the months sort correctly (e.g. in chronological order rather than alphabetical)?

    • You can do this by creating a reference table with the month numbers and names. When you use this table you will need to set up the sort order. To do this you will need to create the table, with ids [1 to 12] then the corresponding month name [Jan to Dec]. Then load the table to the data model. Next highlight the column with the month names, then click on the modeling ribbon, then select Sort by Column, and choose the id column that you made earlier. This will properly arrange the month names by their calendar year order. Then when you are making your visuals, use the Month name instead of the number of the month. Be sure to create a relationship between the month number from your master date table to the month by name table.

  11. Great article. I am new to Power BI and coming from Tableau. One report/dashboard that I did in Tableau for my company was to compare services this year, month, and week with last year, month, and week. Another words, report has Year, Month, and Week filters and when staff chose let’s say 2018, Feb, Week 1 the report populate data and graphs (HeatMap) of 2018 Feb Week 1 with 2017 Feb Week 1. My question is how can I calculate last year month and week services in Power BI? I used Sameperiodlastyear but that only gave me this year as compared to last year and no more. Any suggestions?

    • Time calculations are a bit more challenging within Power BI compared to Tableau. This is due to the use of DAX which is extremely powerful and helps with many of these different types of calculations. I would recommend simplifying the year over year calculations by using date calendars with Year, Month, Day indexes. Try this blog on this topic, towards the end I go through a couple of examples that are specifically calculating sums using the date calendar. https://powerbi.tips/2017/11/creating-a-dax-calendar/

  12. Thank you Mike. The codes for generating the calendar and other calculations were very helpful.

  13. How to calculate YOY growth change,for current Quarter, Month, if in the previous Year Quarter, and Previous Year Month data is only partially available.In other words, how to select current range based on what is available in previous range (previous year) ?

    • You might want to try using the quick measures. Right click on the table where you want to add the measure. Then select New Quick Measure. A menu will appear. In the drop down menu, there will be a Time Intelligence section. Try some of these calculations Year over Year, Month over Month, to see if this is able to calculate what you are looking for.

  14. Hi MIKE,
    Great Article, how can we show the percentage change of 3 years say from 2015 to 2018 (instead of comparing this year and last year)in a single chart.

  15. Hi Mike,

    Please, I would appreciate if you can help with a DAX Measure for Year Over Year Percentage Change. I am trying to calculate the Year over Year Percentage Change for Year 2013 to 2019 Sales Growth (%).
    I could do this MS Excel, in Power Pivot and Power BI I am limited to this Year and Last Year (%) Growth. Thanks.

    • You will need to make a measure that handles something like the following:

      MearsueName =
      VAR year2013 = Calculate( [MeasureSummingColumn], Date[Year] = 2013)
      VAR year2019 = Calculate( [MeasureSummingColumn], Date[Year] = 2019)
      RETURN DIVIDE( year2019 – year2013, year2019)

Comments are closed.