Show only the sales for the last 12 months

Recently I was helping someone on the Power BI community who wanted to only see the sales for the last 12 months in his chart, nothing more. Well this is pretty simple using DAX and I decided to use variables as well. The key here is to think about what you really want and see if you can express this using DAX. In this case it is pretty simple: only show the sum of sales if they happened in the last 12 months.

The first thing we need to do is get the date 12 months ago. I use LASTNONBLANK to get the last date where I had sales, I then use DATEADD to move the time back 12 months and finally I overwrite the filtercontext using ALL to make sure I always get the actual last date, regardless of any filters set on the report. The result is a date that I save into a variable. Initially for debugging purposes I return the date to see if it works.

Salestemp = 
var vrlastdate12mnth =  CALCULATE(DATEADD(
                                          LASTNONBLANK(FactInternetSales[DueDate]
                                                       ,SUM(FactInternetSales[SalesAmount]))
                                          ,-12
                                          ,MONTH)
                                , ALL(FactInternetSales))
return vrlastdate12mnth

Putting this is into a table shows us that the last date we had sales was 8/12/2008. The salestemp measures gives us the correct date one year earlier for each date thanks to the ALL, this is what we need to do the proper comparison later:

Now putting it all together I use the date to determine if we can show the sales only when the current date is larger than the last date 12 months ago:

Sales12 =
var vrlastdate12mnth =  CALCULATE(DATEADD(
                                          LASTNONBLANK(FactInternetSales[DueDate]
                                                       ,SUM(FactInternetSales[SalesAmount]))
                                          ,-12
                                          ,MONTH)
return IF(MAX(DimDate[FullDateAlternateKey]) >= vrlastdate12mnth
         , SUM(FactInternetSales[SalesAmount]))

Observe I use the the max date to determine the current date instead of values, this will allow the measure also to work on a grand total.

Putting the new measure (dark color) into a visual together with the regular sum of salesamount (greenish color) immediately shows us that only the last 12 months get returned in our Sales12 measure: