THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
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.


            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).


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: , ,



dax newbie said:


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 ?


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:


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,


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

The book is

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.


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.



March 27, 2015 12:58 PM

Marco Russo (SQLBI) said:


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

Riyan said:

I want to calculate 6 weeks sales prior to starting of fiscal year.i have date column and a condition




i am trying to return firstdate of the fiscal year and then subtract 42(which means 6 weeks * 7 days= 42).to get the sales for 6 weeks. but i am not getting any results.

April 5, 2016 1:36 PM

Marco Russo (SQLBI) said:

Consider that FIRSTDATE is like LASTDATE and suffer of the same issue described in the blog post. You should use MIN instead and you should remove the effects of the context transition (because you call CALCULATE) by applying ALL in the filter arguments of the two CALCULATE calls.





       >= CALCULATE (

           MIN ( 'SIMDate'[WEEKSTART] ),

           'SIMDate'[PRESENTYEAR] = "CURRENT",

           ALL ( SIMDate )


           - 42,


       <= CALCULATE (

           FIRSTDATE ( 'SIMDate'[WEEKSTART] ),

           'SIMDate'[PRESENTYEAR] = "CURRENT",

           ALL ( SIMDate )



April 16, 2016 3:46 PM

EmilioA said:


I am lost about a data validation check with I need to perform in rows containing many dates which need to be in order (all the dates on each of the rows) since original forms do not yet validate these dates.


ID | Date.Received | D.Registered | D.Analysed | D.Reviewed | D.Authorised

I started in DAX (with a stick) using 'if' statements and comparing in consecutive pairs. But sometimes I find blanks and I need to compare with some columns ahead. This destroyed me.

Then moved onto Power Query 'M' language since the problem is more ETL. Tried to program a custom column 'IsDateOrderOK', but same wall was awaiting for me...

My mindset is linear-programming, not data-oriented. Any light on the approach I should follow would be extremely appreciated!

Amazing blog, amazing technologies!

Thanks Marco!

October 7, 2016 11:29 AM

EmilioA said:


nothing like a friday to see clear and find the right path...

In PowerQuery; I built a list with the dates to ckeck (nulls removed) and compared it with a sorted copy of it. Cool:

   #"Added Custom" = Table.AddColumn(#"Reordered Columns", "IsDatesOrderOK", each if (List.Sort(List.RemoveNulls({[Request date], [Date Created], [To PFM], [From PFM], [To Analysis], [From analysis], [To IAs], [From IAs], [IARM], [To Director], [To authority], [From authority]})) = List.RemoveNulls({[Request date], [Date Created], [To PFM], [From PFM], [To Analysis], [From analysis], [To IAs], [From IAs], [IARM], [To Director], [To authority], [From authority]})) then true else false),

Now I have a column indicatint the health of the dates.

Hope it helps somebody,

Wonderful weekend to all!!!

October 14, 2016 11:23 AM

Marco Russo (SQLBI) said:

Thanks Emilio for reporting the solution!

January 19, 2017 2:12 PM

harsh said:

a very helpful post, thanks!

May 15, 2018 9:24 PM
New Comments to this post are disabled

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



Privacy Statement