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
 

Jay said:

Hi Marco,

I am new to BI and DAX, I need to create a report with Start Date and End Date slicers and two pie charts. The Start date and End Date table are two separate tables with sequential dates, they each link to the request trans table. Below are the 3 measures I created for the tables:

In StartDate table: Selected_StartDate:=lastdate(StartDates[Date])

In EndDate table: Selected_EndDate:=lastdate(EndDates[Date])

In Request Trans Table:

X_Periods_Value:= calculate(COUNTROWS(Requests),DATESBETWEEN(Requests[CreatedDt],[Selected_StartDate],[Selected_EndDate]))

I created pivot, pie charts, start date slicer and end date slicer which each link to the pie charts, but they are not working when I selected start date and end dates. What did I do wrong, how to make them work? Please help. Thanks in advance. Jay

March 20, 2015 12:45 AM
 

Marco Russo (SQLBI) said:

If you have relationships between tables, you should disable them - when you select the two tables, both filter the transactions in different days, resulting in no rows available. If you have our Power Pivot book (or video course), this is covered in the sections about time-related calculations.

March 21, 2015 3:48 AM
 

Jay said:

Hi Marco,

Thanks for the reply. I have two additional questions according to your reply:

1.  How I can disable the relationships between Start date and End Date tables, but still can filter the transaction data?

2. Which video course you are referring, or what is the title of the Power Pivot book you are talking about?

Thanks in advance for your help,

Jay

March 23, 2015 2:38 PM
 

Marco Russo (SQLBI) said:

1) You can make relationships inactive in the diagram view and then enable them in your DAX expression using USERELATIONSHIP

2) The video course is http://www.powerpivotworkshop.com/courses/#recorded

The book is http://www.sqlbi.com/books/excel-2013-building-data-models-with-powerpivot/

March 23, 2015 3:04 PM
 

Jay said:

Thanks Marco for the information. question: what if I combine the start date and end date into one table, but use start date and end date as slicer for user to select dates, should I still use USERELATIONSHIP function?  

I tried to combine them, then use the same formula:

X_Periods_Value :=CALCULATE(COUNT(Requests[RequestID]), Requests[CreatedDt] >=  [Selected_StartDate] && Requests[CreatedDt] <=  [Selected_EndDate])

it still not working, can you help fix it? Thanks ahead.

Jay

March 24, 2015 5:12 PM
 

Jay said:

Hi Marco, I am interested in taking the Power Pivot Complete ($224) training, it said it is a bundle course, what does "bundle" means? it is a video training? Does it include book or course material? Please let me know.

Thanks,

Jay

March 27, 2015 12:58 PM
 

Marco Russo (SQLBI) said:

Jay,

regarding the question about the Requests formula, I don't know how you defined Selected_StartDate and Selected_EndDate. It is not clear what you are trying to do from a data model point of view.

Regarding the "bundle", it is the package of three video courses (beginner, intermediate, and advanced).

I hope it helps.

April 1, 2015 5:51 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