|A Different Approach To Last-Ever Non-Empty in DAX|
|Written by Chris Webb|
|Tuesday, 15 January 2013 21:13|
Reposted from Chris Webb's blog with the author's permission.
The post I wrote on the last-ever non-empty problem in MDX has been by far the most popular post I've ever written. It was the most popular post on my blog in 2012, and I wrote it in 2011! I hadn't thought about how to solve the problem in DAX though, and when a few months ago Javier Guillen wrote an excellent post on exactly this subject I thought it wasn't worth bothering with any more.
However, I changed my mind when was writing some DAX for a PowerPivot project recently and came across a totally different way to solve this problem which I thought I should write about. I'm not sure whether this approach is better or worse than Javier's in terms of performance or maintainability, but it returns the same values as my original MDX solution and I'm sure those of you out there who like DAX would be interested in seeing it.
First of all, here's the SSAS Tabular model I'm using for this post, which uses data from Adventure Works DW:
At the core of this approach is the idea that when you're searching for the last non empty date on which a sale was made, all you need to do is this:
Here's a simple measure that illustrates this approach:
Last Ever Sales Date:=
Running the following MDX query against the Tabular model (yes, I know, I'm using an MDX query, but wanted to have Customers on columns for testing purposes!) shows that it does indeed return the last ever non empty sales date:
If we then want to return the value of [Sum of Sales Amount] for the last ever non empty date, then we just need to do this:
LENE Sales Date:=
However, there's a problem here: this measure works at the Date level, but it doesn't return what you'd expect (or at least it isn't consistent with my original MDX solution) at the year level. What happens if someone buys on two different dates in the same year? This code will still return the value of [Sum of Sales Amount] for the last sales date, not the value of [Sum of Sales Amount] for the last year that had a value. For example, take customer Carl A. She:
Carl made two purchases on different dates in 2007, but this calculation returns only the value of the last purchase.
To get around this, I had to use some conditional logic. At the year level, instead of returning the sales for the last ever date that had a sale, what I actually want is the sales for all dates from the last ever date that had a sale back to the beginning of the year that contains that last date:
LENE Sales Year:=
Here, instead of using LastDate, I've used DatesYTD to get that table of dates. The same problem happens at Month level too, so DatesMTD must be used:
LENE Sales Month:=
Finally, a measure that detects whether we're looking at a Year, Month or Date is necessary so the right value can be returned:
Here's a query, using a hierarchy on DimDate that contains Year, Month and Date levels, to show the results:
I can't help wondering whether there's a better way to solve this problem of getting the Year, Month and Date values correct. but that's a topic for a separate post I think.