Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Greg_Deckler

In Defense of EARLIER

Introduction

Some consider EARLIER a relic of the bygone era when DAX did not support variables, criticize it's name and do not recommend using it. I was even given a negative review on my book, DAX Cookbook, apparently largely because I happened to use the EARLIER function in a scant few of the over 120+ DAX recipes contained within that tome. The DAX Guide screams at the reader, flagging the EARLIER function with a bold, red, and all caps NOT RECOMMENDED tag. However, I find the criticism of EARLIER to be overstated and inconsistent. Here's why.

 

Utility and No Longer Needed

While it is true that EARLIER is technically no longer required if you use VAR and RETURN statements carefully, is that a reason for criticism? There are tons of sugar syntax, "useless" DAX functions out there and yet they are not equally criticized or branded with NOT RECOMMENDED tags.

 

For example, LASTDATE and FIRSTDATE. First, what in the world are people thinking creating a functions that essentially find the MAX or MIN of only a date data type? Second, these functions are not needed and have no utility, they are exactly equivalent to { MAX() } and { MIN() }. And yet, these functions receive a pass while EARLIER does not.

 

Similarly, STARTOFYEAR, ENDOFYEAR, STARTOFQUARTER, ENDOFQUARTER, STARTOFMONTH, ENDOFMONTH? Face palm. These are actually, IRL, just hamstringed MIN and MAX. They don't even do what they say they do. You feed STARTOFYEAR a date column that goes from January 2nd to some other date and it is going to return January 2nd. It isn't even smart enough to know that years start on January 1st. Feed STARTOFYEAR and ENDOFYEAR a date column that spans two years, you wind up with a STARTOFYEAR in one year and an ENDOFYEAR in another. 

 

Another example, DISTINCTCOUNT. Why? First, it is constrained to only accepting a column as a parameter. Second, it is exactly equivalent to COUNTROWS(DISTINCT(...)). And, the longer form allows you to specify a column or a table. And yet, despite being useless, DISTINCTCOUNT garners no hatred.

 

Bottom line, it is simply inconsistent to criticize EARLIER simply because alternative syntax exists when you do not also point out the same thing with other DAX functions. And, at least EARLIER has the utility of being concise. This is really important, for example, when you are writing a book that contains really long DAX formulas and eliminating two rows for a VAR and RETURN statements keeps the formula from running from one page to another...I'm looking at you, anonymous "Amazon Customer" reviewer person.

 

Poorly Named

The EARLIER function returns the value of the specified column within an earlier row context. While some have argued that the function should be called OUTER or CURRENT or something similar, there really isn't a name that would make it any clearer what EARLIER does. In fact, if you read that first sentence carefully, EARLIER seems like the right name. But again, is EARLIER the only poorly named function in DAX?

 

Let's pick on STARTOFYEAR, ENDOFYEAR, STARTOFQUARTER, ENDOFQUARTER, STARTOFMONTH and ENDOFMONTH again. They don't do what their names suggest. They should all be named two functions. MINDATE and MAXDATE. After all, that's what they really do. 

 

And again, FIRSTDATE and LASTDATE. Shouldn't these really be called FIRSTDATEASTABLE and LASTDATEASTABLE? Shouldn't we be warned that these functions return a single scalar value as a one row table? Which, BTW is weird and kind of tragic.

 

Let's continue with SEARCH and FIND. First, shouldn't these just be one function with a case-sensitivity flag parameter? Or if you keep them separate shouldn't they be FINDCASE and FINDCASEINSENSITIVE? How in the world is one to know that FIND is case-sensitive while SEARCH is not? Honestly, I always have to look that one up to be certain.

 

And let's not forget about REPT. Really? It would kill you to just add the EA in there, REPEAT? We have functions with long names like SUBSTITUTE, why isn't that one SUBST or SUB following the same logic? The answer, SUBSTITUTE is a good name and REPT is an awful, awful name.

 

So, again, picking on EARLIER for being "poorly named" seems capricious and unfair.

 

Hard to Understand and Use

Let's state the functionality of EARLIER once again. The EARLIER function returns the value of the specified column within an earlier row context. Is this sentence really all that hard to understand if you understand context? Doesn't seem like it. But is EARLIER really the most difficult function to understand and use given functions like RANKX and the entire library of "time intelligence" functions? I think RANKX and TI functions keep professional bloggers employed 24/7 trying to explain their quirks. Or what if you are trying to use MEDIAN in a column formula? Now that is downright bizarre and hard to understand. So EARLIER the most difficult, hard to understand and use function in DAX? Not by a long shot.

 

Less Readable and Harder to Maintain

The final argument that I will cover is the claim that using EARLIER makes the code less readable and hard to maintain. Does it really? If you don't know how to use VAR and RETURN statements then code that uses VAR and RETURN is less readable and harder to maintain. This is no different than if you don't know how to use EARLIER. But if you know how to use EARLIER then the code is obvious and easy to maintain.

 

Or are we saying that you should never nest DAX functions one inside another and instead use VAR statements for everything that you would otherwise nest? Because, I'm pretty certain that there is a metric **bleep** ton of nested functions in DAX formulas out there in the wild. So, are we to conclude then that all of that code that uses nested DAX functions is less readable and harder to maintain?

 

Conclusion

I find the criticism of EARLIER to be inconsistent, capricious and downright mean-spirited. I for one find the use of EARLIER convenient and concise. The criticisms leveled against EARLIER can be leveled against a large number of DAX functions and yet they are not. Why then EARLIER deserves to be singled out for persecution and NOT RECOMMENDED is anyone's guess.

Comments

I also use EARLIER in situations where defining a variable seems like overkill.

 

Renaming it to something like OUTER would make it less likely to be confused with a Time Intelligence function (which I've seen several people do). Because of this confusion, I generally feel obligated to explain that it has nothing to do with time whenever I share a DAX expression that includes EARLIER.

Hi Greg,

Nice Article but I do have a question to ask
How do you create a calculate column in a table displaying sub total per group and total ?

 

Thanks,

Oded Dror

@Oded-Dror Not certain I quite understand completely but you could do something like:

 

Column = SUMX(FILTER(ALL('Table'),[Category] = EARLIER('Table'[Category])),[Value])

 

All of the rows with the same Category would end up with the same total value.

Greg,

 

Thank you

 

Oded dror