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:
Hi Kasper!
Nice work! But… if we want to see real 12 month from today, then you formula will show not from today, but from last date with sales? For example, now is July, and I expected sales from last 12 months (July 14, 2015 – July 13, 2016, or something like that), but last sales was in May. Will your formula calculate Jul-Jul, or May-May?
Correct, the formula will use May.
If you want to use the current day you can just do something like
var vrlastdate12mnth = TODAY()-366
instead.
Could you not take TODAY and then get LastYEARtoday using DATEADD -1 year. Then calculate ( [measure], datesbetween( dimDate(Date), TODAY, LastYEARtoday )) ??
Any change of us getting a download link of your sample. Great post anyway 😀.
Very interesting solution, thanks Kasper.
Struggling with a task, which is slightly more complex, actually, extending this example.
Imaging, we have a slicer Year / Month. The goal is – to display small chart (like a sparkline in Excel) for R4m or R12m, which should be rendered relatively to selected month. I tangled in filter context…
In this post you have anchoring date – latest date; if nothing is selected on slicer – then as well can be taken latest data as anchor. However, if something is selected – then max date of selected period must be an anchor… but then come a chart where each point is a filter context for DAX expression together with a value coming from slicer…
Kindly ask you for advice 🙂 Probably, it is not possible in the end…
Kasper,
Is this akin to a “would-be rolling sum” except that there is an upper boundary? I take it the reason you do this is because you do not want calculations showing up where there is no data. For example, you want “blank” showing for the range of 8/15/2007 through 8/14/2008.
Thanks.
can’t work out how to “move” the range of the 12 months based on a date filter… 😐
You should just use the basic measure. And why would you want to build a measure that does this. Why not build a last twelve months indicator field in your date dimension?
Question for you: how will this work if my dates are all marked as first of the month (i.e Sales in March are all labeled as 3/1/2020).
I would want to get the products with no sales in the last 18 months?
Thanks in advance!