Measures – Month to Month Percent Change

Feature Image Month to Month Percent Change

I had an interesting comment come up in conversation about how to calculate a percent change within a time series data set.  For this instance we have data of employee badges that have been scanned into a building by date.  Thus, there is a list of Badge IDs and date fields.  See Example of data below:

Employee ID and Dates
Employee ID and Dates

Looking at this data I may want to understand an which employees and when do they scan into a building over time.  Breaking this down further I may want to review Q1 of 2014 to Q1 of 2015 to see if the employee’s attendance increased or decreased.

Here is the raw data we will be working with, Employee IDs Raw Data.  Our first step is to Load this data into PowerBI.  I have already generated the Advanced Editor query to load this file.  You can use the following code to load the Employee ID data:

let
 Source = Csv.Document(File.Contents("C:\Users\Mike\Desktop\Employee IDs.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
 #"Promoted Headers" = Table.PromoteHeaders(Source),
 #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Employee ID", Int64.Type}, {"Date", type date}}),
 #"Sorted Rows1" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
 #"Calculated Start of Month" = Table.TransformColumns(#"Sorted Rows1",{{"Date", Date.StartOfMonth, type date}}),
 #"Grouped Rows" = Table.Group(#"Calculated Start of Month", {"Date"}, {{"Scans", each List.Sum([Employee ID]), type number}})
in
 #"Grouped Rows"

Note: I have highlighted Mike in red because this is custom to my computer, thus, when you’re using this code you will want to change the file location for your computer.  For this example I extracted the Employee ID.csv file to my desktop. For more help on using the advanced editor reference this tutorial on how to open the advance editor and change the code, located here

Next name the query Employee IDs, then Close & Apply on the Home ribbon to load the data.

Close and Apply
Close and Apply

Next we will build a series of measures that will calculate our time ranges which we will use to calculate our Percent Change (% Change) from month to month.

 

Now build the following measures:

Total Scans, sums up the total numbers of badge scans.

Total Scans = SUM('Employee IDs'[Scans])

Prior Month Scans, calculates the sum of all scans from the prior month.  Note we use the PreviousMonth() DAX formula.

Prior Month Scans = CALCULATE([Total Scans], PREVIOUSMONTH('Employee IDs'[Date]))

Finally we calculate the % change between the actual month, and the previous month with the % Change measure.

% Change = DIVIDE([Total Scans], [Prior Month Scans], blank())-1

Completing the new measures your Fields list should look like the following:

New Measures Created
New Measures Created

Now we are ready to build some visuals.  First we will build a table like the following to show you how the data is being calculated in our measures.

Table of Dates
Table of Dates

When we first add the Date field to the chart we have a list of dates by Year, Quarter, Month, and Day.  This is not what we want.  Rather we would like to just see the actual date values.  To change this click the down arrow next to the field labeled Date and then select from the drop down the Date field.  This will change the date field to be viewed as an actual date and not a date hierarchy.

Change from Date Hierarchy
Change from Date Hierarchy

Now add the Total Scans, Prior Month Scans, and % Change measures.  Your table should now look like the following:

Date Table
Date Table

The column that has % Change does not look right, so highlight the measure called % Change and on the Modeling ribbon change the Format to Percentage.

Change Percentage Format
Change Percentage Format

Finally now note what is happening in the table with the counts totaled next to each other.

Final Table
Final Table

Now adding a Bar chart will yield the following.  Add the proper fields to the visual.  When your done your chart should look like the following:

Add Bar Chart
Add Bar Chart

To add a bit of flair to the chart you can select the Properties button on the Visualizations pane.  Open the Data Colors section change the minimum color to red, the maximum color to green and then type the numbers in the Min, Center and Max.

Changing Bar Chart Colors
Changing Bar Chart Colors

Well, that is it, Thanks for stopping by.  Make sure to share if you like what you see.  Till next week.

Want to learn more about PowerBI and Using DAX.  Check out this great book from Rob Collie talking the power of DAX.  The book covers topics applicable for both PowerBI and Power Pivot inside excel.  I’ve personally read it and Rob has a great way of interjecting some fun humor while teaching you the essentials of DAX.

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

17 Comments

  1. Hi Mike. Thanks for the post.
    I actually am having issues with the previous month DAX function. I followed all of the steps as instructed. But I get a blank row for the Prior month scans measure(not sure why). Also tried the DateAdd function and same results. Do you think it has to do with versions of PBI Desktop? I just downloaded the latest one and have same issues there. let me know if you can think of anything that might cause this.

    • Divya, Thanks for the comment. I had the same issue when I first created this tutorial. The issue was the date field was in hierarchy mode. Once that is removed the table of data should present it’s self correctly. I double checked this on the latest version of PowerBI Desktop. I’m running 2.38.4491.642 64-bit (August 2016). Hope this fixes it. I will also try to email you the solution. Good luck, and keep on rockin the PowerBI!

  2. This is super helpful! One question I have is whether it is possible to do this with any prior value that is not a date. For example, I work with higher education data, and frequently we want to compare semesters, which do not have clean month by month dates. Fall semester could start in August one year and September the following year, and the labels in the visualization need to show the name of the term. Another question is whether this is the correct calculation for percent change. I always thought it was the absolute difference in values divided by the original value, not the new value divided by the old value. Anyway, great article! Cheers!

    • Glad you enjoyed this tutorial. I would recommend making a table that would connect your quarters to actual dates. That way you could use the PreviousMonth DAX calculation to determine the month to month percent change.

      As far as the percent change calculation. There are two ways to calculate this. I decided to choose the simpler looking calculation to me. You can calculate percent change by Pct Chng = (New / Old) -1 or Pct Chng = (New – Old) / Old, Here is the simplified equation from Wolfram alpha.

  3. Thanks that formula was helpful. The problem I’m having is that I don’t want dates to be displayed on my table/chart like this:
    Term date Percent change
    1/1/2014 -3.2%
    2/1/2015 2.1%

    I would like the corresponding term names to be there like this:
    Term name Percent change
    2014 Spring -3.2%
    2015 Spring 2.1%

    Is this something that is possible at this point?
    Thank you for these articles!

  4. I’m stuck. I’m using GA and my boss needs me to calculate session %change from sept.2015 to sept.2016. I know how to do this in SQL but not in DAX. Can anyone help me?

    • You should be able to build a couple of measures.
      Here is what I used from DAX to build three measures:
      ThisYear = CALCULATE(SUM(Data[Sales]),Data[Year] = YEAR(Now()))
      LastYear = CALCULATE(SUM(Data[Sales]),Data[Year] = YEAR(Now())-1)
      YoY Percent Change = [ThisYear]/[LastYear]-1

      In addition to the monthly data I made two new columns, one called Month, and one called Year. These are used to tell the measures to filter down the data before running the sum.

      I will also send you a PBIX file with the solution.

  5. Just want to say that this post (and others as I am finding out) was extremely helpful and helped me solve a data request I had. Keep up the good work!

    • Thanks so much, I really appreciate the feedback! I have really enjoyed learning the Power BI Desktop program. I figured if I was struggling through to learn, why not share it so everyone else can also learn.

  6. Hi there,
    Thanks for the detailed explanation. I’ve had a go at this and I get an issue witht he prior month value being assigned to each day when I remove the heirarchy. When I transfer this to a matrix to sum TotalScans by month I get only blank rows for the prior month value and the total for that column is the value for the period prior to the beginning of the date range (e.g Dec 15 was 833, and I dsiplay values from Jan-Nov 16). Any suggestions would be greatfully received.
    Cheers,
    Chris.

    • If I understand your question correctly it sounds like your source data contains data for each day. In this particular tutorial the data was pre-aggregated into monthly totals. See sample of raw data used to support this calculation, Monthly Sales Totals. If your raw data is coming into your data model at a daily increment you will need an intermediate step to summarize the data into monthly buckets.

      You can do this by making a reference query to your original query, adding a reference column that identified the start of the month based on each date you have. Then Group the data by the new start of month field. This aggregates the data down to one number per month increment. Then the measures in the tutorial will work for calculating month over month percent changes. I have worked up a simple demo of this and I’ll send it to you via email. Hope that helps.

  7. Hey Mike,

    Great stuff! I’m trying to apply your logic to a year’s worth of financial data that’s all in one table, and I can’t get it to work. I’m convinced it’s because I’m missing some piece of data that’s coming from your manipulations you’re doing in the Advanced Query (specifically the Transform, calculation and grouping). Could you provide an explanation of what you’re doing, and how to accomplish it (without hand cranking the query if possible)? You can take this offline and email me directly if you like.

    Thanks in advance.

    bt

    • Sure, I’d be happy to help. Please reach out to me at mike@powerbi.tips and I’ll take a look. It would be nice if you could provide me some screen shots so I can understand what your trying to do.

  8. Hi Mike

    Shame there isnt a PREVIOUSWEEK function. Is there a simple workaround to get this? All my data requests are for weekly comparisons.

    • Yes, there is a PreviousWeek function. At the time I was a little less familar with DAX, thus this is the solution I came up with. You can also get really creative with previous time period calculations and follow the instructions at PowerPivotPro.com, which I highly recommend.

Leave a Reply

Your email address will not be published.


*