In a previous blog post we discussed how to replace the total row with a blank value, primarily to eliminate confusion. You can find the original post here: Unexpected Totals in DAX (Part 1)

In this post we want to go a step further and replace the total row with our own DAX calculation.

Our goal is to replace the value of 8,691 with the value of 1,005. The value of 1,005 is the total number of new homes listed on the market in 2016, which in our case is the last year or current year of the data set we are looking at. This is slightly more challenging though, because our date table goes to the year 2018. But just wait, we will get to that shortly.

Let’s take a look at the steps to solve this problem:

1. Identify if the calculation is at the total row, we will use HASONEVALUE as we did in the previous blog post.
2. Determine the MAX year in the data set with homes on market, not the last year in your date table, but the last year with actual homes listed on the market.
3. Write a calculation that returns the New Homes on Market for the last year in the data set.

I am going to create a new measure so we can look at the two measures side by side.

I have modified the original DAX calculation, here we first check to see if we are at the total row using HASONEVALUE, if we are at the total row then we return blank. If this doesn’t make sense, please stop and go back to Part 1 where I cover this in detail.

• A = Check to see if the current filter context has one value, if not, then we are at total row.
• B = If there is more than one year in the filter context, replace with a blank value.

Determine the last year with homes listed.

Now that the total row has been identified, it’s time to author a DAX formula that returns the total homes listed for the last year in our data. The last year with homes listed in our data is 2016, therefore we need to write a DAX formula that reads like this:

Return the number of new homes listed in 2016.

Now, technically we can’t write the year 2016 in our formula because we know that this would no longer work once we move into the next year and we need our DAX formula to be dynamic (automated) and change with the years. Here is our first attempt at getting the MAX year.

• A = Return the MAX year in the Filter Context
• B = Return the MAX year from the variable at the total row.

For demo and validation purposes I am displaying the results of the variable in the total row (The max year), here we can see that the results are maybe not what we would have expected. The year 2018 is the last year in our date table but not the last year that new homes were listed. One way to get the last year with homes listed is to use the function LASTNONBLANK.

LASTNONBLANK

For the sake of brevity I won’t cover LASTNONBLANK here, but I will do a separate blog series on semi-additive measures. Let’s rewrite the DAX formula:

Using LASTNONBLANK we now get the last year that we had new homes listed. See results below:

Perfect! The hard part is over, now we simply count the total rows where the listing date of the home equals the year 2016.

Here is the final result:

As always, thanks for reading and I hope this helped!