THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Difference between LASTDATE and MAX for semi-additive measures in #DAX

I recently wrote an article on SQLBI about the semi-additive measures in DAX. I included the formulas common calculations and there is an interesting point that worth a longer digression: the difference between LASTDATE and MAX (which is similar to FIRSTDATE and MIN – I just describe the former, for the latter just replace the correspondent names).

LASTDATE is a dax function that receives an argument that has to be a date column and returns the last date active in the current filter context. Apparently, it is the same value returned by MAX, which returns the maximum value of the argument in the current filter context. Of course, MAX can receive any numeric type (including date), whereas LASTDATE only accepts a column of type date. But overall, they seems identical in the result. However, the difference is a semantic one. In fact, this expression:

LASTDATE ( 'Date'[Date] )

could be also rewritten as:

FILTER ( VALUES ( 'Date'[Date] ), 'Date'[Date] = MAX ( 'Date'[Date] ) )

LASTDATE is a function that returns a table with a single column and one row, whereas MAX returns a scalar value. In DAX, any expression with one row and one column can be automatically converted into the corresponding scalar value of the single cell returned. The opposite is not true. So you can use LASTDATE in any expression where a table or a scalar is required, but MAX can be used only where a scalar expression is expected.

Since LASTDATE returns a table, you can use it in any expression that expects a table as an argument, such as COUNTROWS. In fact, you can write this expression:

COUNTROWS ( LASTDATE ( 'Date'[Date] ) )

which will always return 1 or BLANK (if there are no dates active in the current filter context). You cannot pass MAX as an argument of COUNTROWS.

You can pass to LASTDATE a reference to a column or any table expression that returns a column. The following two syntaxes are semantically identical:

LASTDATE ( 'Date'[Date] )
LASTDATE ( VALUES ( 'Date'[Date] ) )

The result is the same and the use of VALUES is not required because it is implicit in the first syntax, unless you have a row context active. In that case, be careful that using in a row context the LASTDATE function with a direct column reference will produce a context transition (the row context is transformed into a filter context) that hides the external filter context, whereas using VALUES in the argument preserve the existing filter context without applying the context transition of the row context (see the columns LastDate and Values in the following query and result).

You can use any other table expressions (including a FILTER) as LASTDATE argument. For example, the following expression will always return the last date available in the Date table, regardless of the current filter context:

LASTDATE ( ALL ( 'Date'[Date] ) )

The following query recap the result produced by the different syntaxes described.

EVALUATE
    CALCULATETABLE
(
        ADDCOLUMNS

            VALUES ('Date'[Date] ),
            "LastDate", LASTDATE( 'Date'[Date] ),
            "Values", LASTDATE( VALUES ( 'Date'[Date] ) ),
            "Filter", LASTDATE( FILTER ( VALUES ( 'Date'[Date] ), 'Date'[Date] = MAX ( 'Date'[Date] ) ) ),
            "All", LASTDATE( ALL ( 'Date'[Date] ) ),
            "Max", MAX( 'Date'[Date] )
        ),
        'Date'[Calendar Year] = 2008
    )
ORDER BY 'Date'[Date]

The LastDate columns repeat the current date, because the context transition happens within the ADDCOLUMNS. The Values column preserve the existing filter context from being replaced by the context transition, so the result corresponds to the last day in year 2008 (which is filtered in the external CALCULATETABLE). The Filter column works like the Values one, even if we use the FILTER instead of the LASTDATE approach. The All column shows the result of LASTDATE ( ALL ( ‘Date’[Date] ) ) that ignores the filter on Calendar Year (in fact the date returned is in year 2010). Finally, the Max column shows the result of the MAX formula, which is the easiest to use and only don’t return a table if you need it (like in a filter argument of CALCULATE or CALCULATETABLE, where using LASTDATE is shorter).

image

I know that using LASTDATE in complex expressions might create some issue. In my experience, the fact that a context transition happens automatically in presence of a row context is the main reason of confusion and unexpected results in DAX formulas using this function. For a reference of DAX formulas using MAX and LASTDATE, read my article about semi-additive measures in DAX.

Published Tuesday, October 22, 2013 3:22 PM by Marco Russo (SQLBI)
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

dax newbie said:

Marco,

As far as I understand VALUES function is evaluated first.

And then result of VALUES is filtered by CALCULATETABLE.

But how does it possible to filter by year when VALUES returns only one column Date column, not the year ?

Thanks.

December 5, 2013 10:16 AM
 

Marco Russo (SQLBI) said:

VALUES returns the values active in the filter context at the moment of call; it is called within an ADDCOLUMNS that is the first argument of a CALCULATETABLE function, so it is evaluated *after* the CALCULATETABLE filter takes place. For this reason it is filtered by the year.

I suggest you to study the filter context and evaluation context concepts before, otherwise trying to reverse engineer the logic of the DAX code is really hard!

(you can find detailed explanation in our books about Tabular and PowerPivot here: http://www.sqlbi.com/books)

Marco

December 6, 2013 5:12 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement