For this week’s project, I’ve been looking at trending data. More specifically, I was tasked with showing the previous 12 months data for some metrics as a comparative guide. How to do this wasn’t immediately obvious to me; and it took a bit of trial and error to winnow down my approaches. I’m pretty happy with this method for rolling months, but if you can think of a better way then let me know on twitter!

Imagine these hills are months… (Image Jay Huang)

First off, let’s make a parameter called ‘Month’ to let us test our solution. Since we’re only interested in months, then I’m going to choose June 2016 as our date format and add our list of values from [Order Date].

Next up, it’s calculated field time. I’m going to call mine ‘Last 12 Months’ and you’ll want to set it up as follows:

The reason we want ‘<12’ is because DATEDIFF starts counting from ‘0’. The second half of the field is to remove those months that come after the one selected by our parameter. Put this field on filter and select ‘true’.

As you can see, as we change our parameter, our filter only retains the previous 12 months.

I thought this was pretty cool, and I hope it is useful for you 😊