DAX LASTDATE vs. LASTNONBLANK

In my previous blog post I discussed how the time intelligence function CLOSINGBALANCEMONTH worked great, except when it didn’t. If you remember, the problem was the function CLOSINGBALANCEMONTH could not handle situations where the data had gaps, or blanks on specific dates.

If you missed the previous blog post, you can find it here:

In this post,  I want to build a measure that returns the closing price for the date in the current context. My initial attempt at this measure is going to suffer from the same issue we faced when working with the CLOSINGBALANCEMONTH function in the last blog. Let’s start by using CALCULATE and LASTDATE.

Working with LASTDATE

Here is your MSDN definition of LASTDATE: “returns the last date in the current context for the specified column of dates”.

This function is great because it works in the current context so it makes the measures you author in DAX very dynamic. For example:

  • If the current context is month, LASTDATE returns the last day of the month.
  • If the current context is Quarter, LASTDATE returns the last day of the quarter.
  • If the current context is day, the day in the current context is returned.

This means that the LASTDATE function automatically works for each level in your date hierarchy, this is why we like working with DAX, because of this type of functionality.

Let’s take a look at a simple example of LASTDATE:

image

This simple calculated measures returns the following:

image

As you can see in this screenshot, LASTDATE is returning the last date of the current context, in this visual we have the month and the year.

Next, I will create a new measure that returns the Closing Price of the current time period. The following measure returns the Closing Price of the stock for the last day of the month:

image

If we take a look at the results in our table visual, we will see that our measure is returning blanks for certain months. The reason this is occurring is because the stock market isn’t open every day of the year, therefore, if there is no closing price for the last day of the month then a blank value is returned.

image

LASTNONBLANK function in DAX

I discussed the LASTNONBLANK function in my previous blog post, so I won’t get too detailed here but here is the definition and syntax from MSDN:

Definition: Returns the last value in the column, filtered by the current context, where the expression is not blank.

Syntax: LASTNONBLANK(<column>, <expression>)

LASTNONBLANK will return the last date in the current context that wasn’t blank, that is the perfect function for this scenario.

image

Let’s take a look at the results of LASTNONBLANK compared to LASTDATE. In the highlighted sections below, notice that for each area where the close price is blank the results of the LASTDATE function and LASTNONBLANK function differ. As previously discussed, the stock market was closed on the last date of the month and therefore the close price does not return a value.

image

Now it’s time to modify the Close Price measure so that it returns the last close price for the current context:

image

Here are the final results:

image

Thanks for reading, enjoy!

Semi additive measures in DAX and Closingbalancemonth

It’s been a while since we have visited Data Analysis Expressions (DAX) on this blog, but now we’re going to jump right in and discuss working with semi-additive measures. Semi-additive measures can’t be added across all dimensions, typically they can’t be added across the date/time dimension. Common examples of semi-additive measures are account balances and inventory levels.

  • Inventory levels can be added up across products, across different stores, but not across time. If you have 500 silver widgets at the end of day on Monday and you have 500 silver widgets at the end of day on Tuesday, how many widgets do you have? You only have 500 of course! We have to take this into consideration when building our model and measures.
  • The same is true of account balances as well. If I have $100 in my account on February 1st and I have $85 in my account on March 1st, what is my account balance? It’s only $85, it’s not the sum of both months.

The Scenario

In this scenario, I am looking at the stock price of Microsoft over time. We want to determine things like Closing and Opening price among others. For this example we are going to try and calculate the closing price for the month using the function CLOSINGBALANCEMONTH. This will present an interesting problem that we will discuss shortly. First, my data model has a simple measure which returns a SUM of the closing stock price as seen below:

image

Remember, this measure is valid for all the dimensions in my data model except for my date dimension. Similar to inventory levels and account balances we don’t want to add the closing stock price across time, this produces incorrect results. Back in 2012 the stock price of MSFT stock was around $30 a share, however, when I display our measure in a table with the year and month what we actually see are numbers that are much higher. This is because the measure is adding up the stock price for all days of the month and this is incorrect!

image

CLOSINGBALANCEMONTH

Definition: Evaluates the expression of the last date of the month in the current context.

Syntax: CLOSINGBALANCEMONTH( <expression>, <dates>)

CLOSINGBALANCEMONTH is one of the built in time intelligence functions and it works great, most of the time. Where it falls short is when you have blanks or gaps in your data. I am going to create a new measure using CLOSINGBALANCEMONTH using the following expression:

image

Now let’s take a look at the results:

image

Most months we are getting the correct value, but some months are blank, why? CLOSINGBALANCEMONTH returns the closing price of the stock for the last day of the month, unfortunately we are looking at stock market data and the stock market is not open every day of the month. So therefore, if the last day of the month has no closing stock price, then blank is returned. See screenshot below. This is typically not what we want when looking at semi-additive measures! We want to return the closing balance for the last day of the month that had a value.

LASTNONBLANK IN DAX to handle blanks!

We need to write a measure in DAX that is going to determine the last date of the month where the stock market was open. We are going to solve this problem by using the function LASTNONBLANK. This is an extremely useful and helpful function.

Definition: Returns the last value in the column, filtered by the current context, where the expression is not blank.

Syntax: LASTNONBLANK(<column>, <expression>)

I am going to build this out incrementally for demonstration and validation purposes. First, we are going to create a new measure just to see what this function returns:

image

Next, I will add this new measure to our table for validation. Everything looks perfect! The measure is not blindly returning the last day of the month, it’s returning the last day of the month that had a closing price for the stock, meaning the value returned for June, September, and March is exactly what we need.

image

The False Positive

With our knowledge of DAX we may now attempt modify our Close Price measure with the following:

image

This now returns the following results:

image

BOOM! Winner winner chicken dinner, I’m taking the rest of the day off and going to the beach! Wait a minute…. was the topic false positive?

Are the results above correct? Yes the are, just take my word for it or else this blog post is going to really, really long. However, it’s really easy to author formulas in DAX that work at one level but don’t work at other levels, and this is because of Filter context. As developers we have to always consider how the end users might slice the data. For example, if a user is looking at the data at the day level, will our measure still return he closing price for the month? Let’s check.

image

Immediately, we see two different items that tell us the measure is definitely not returning the end of month close price.

  1. First, the close price and the close price (eom) measure have identical values, this means our closing month measure is displaying the closing price for each individual day. That tricky filter context got us again!
  2. Secondly and most obvious, the close price (eom) values should be identical for every day of the month, they are not. Clearly this measure is not working. Back to the drawing board.

Go back and look at the definition for LASTNONBLANK, it works within the current filter context so when we filter our report down the day level it can only return that day.

PARALLELPERIOD IN DAX

Now it’s time to introduce you to one more function in DAX and that is the PARALLELPERIOD function.

Definition: Returns a table that contains a column of dates that represents a period parallel to the dates in the specified dates column, in the current context, with the dates shifted a number of intervals either forward in time or back in time.

(I just read that definition and now I’m confused….)

Syntax: PARALLELPERIOD(<dates>, <number_of_intervals>,<interval>)

The PARALLELPERIOD function will return all the dates in the interval that we specify within in the current context. What does that mean??? If you were looking at January 1st and you used PARALLELPERIOD to return all the dates at the month level then a table would be returned with all 31 days for January. This means that we can now return the closing price for the month even if the user is exploring the data at the day level!! I can feel your excitement as I write this.

Let’s jump right in and look at the final DAX expression. I am once again going to modify my existing calculated measure, this time I’m replacing ‘date’[date] with PARALLELPERIOD:

image

Here are the final results:
image

Enjoy!

Unexpected Totals in DAX (Part 2)

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.

image_thumb5

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.

image

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.

image

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

image

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:

image

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

image

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

image

Here is the final result:

image

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