Tiny Lizard

Power Pivot Consulting and Training

  • About
  • Blog
  • Power Pivot
  • Training
  • Contact

The DAX EARLIER Function

December 21, 2014 By Scott Senkeresty 10 Comments

When I give a Power Pivot training, this is one of the harder concepts to explain.  It’s not something easy for a complete Power Pivot beginner to understand… but there is a general pattern you can use anyway, without completely understanding it.  Good thing, because it a SUPER useful function.  By the end of this post, you will better understand Row Context, the EARLIER() function… and have the absolute worst stock analysis ever. Smile

Row Context

Before we really dig into this bad boy, the first thing we have to understand is that EARLIER() is all about Row Context.  (And it’s not All About That Bass, which sadly, just jumped into my head).  We talk about Row Context in this previous post, but that post is generally a bit advanced and scary Smile

By far the most common place to have a row context… is when writing a calculated column (as opposed, to a calculated field, aka a measure, the differences are discussed in this post).

imageLet’s find some data… in my favorite way possible.  Using Power Query to search the interwebs!  Ummm… ok, uh… let’s go with “Microsoft”.  Okay, I got something here:  good old msft stock data.

At left we see a snip of my table once I pull it into Power Pivot.  We can’t decide which column of pricing data is more important to us… so, we decide we will just average together the high and low, and pretend that is useful.  So we add a new calculated column.

Avg = (MSFT[High]+MSFT[Low])/2

 

The important thing to realize is that the values for this calculated column are a) only updated when you refresh your data (no amount of slicing and filtering will impact that value) and b) is evaluated one row at a time.  Which, makes intuitive sense… you would probably create that formula without really even thinking about that.  But it is very different from writing a measure / calculated field… when you are evaluating a whole bunch of rows.  This evaluation of one row at a time means we have “a row context”.  Meaning, simply, that when the evaluation is taking place the engine knows exactly which row it is evaluating.  I am taking the [High] of the row in the current context, and adding it with [Low] of the row… in the current context.

Let’s write another table that is a bit more useful… a way to divide the volume into buckets.

=if ([Volume] < 45000000, “Low”, IF ([Volume] > 70000000, “High”, “Medium”))

 

image

This is a common and incredibly useful technique, so remember it Smile  We walked our rows and, for each row, checked the [Volume] column, and if it was more then 70 million rows, we consider that a High volume day.  What is great about this is we can now go drop the Low/Med/High values onto rows of a pivot table or chart and evaluate if those buckets tend to behave differently.

Okay, with that background on row context and calculated columns behind us… let’s jump into the harder part…

Earlier() Function

We are going to kind of … guess our way to the need for this function.   Let’s say that we decide we think these volume buckets are the key to our investing genius and a fat fat retirement fund.  We decide that we want to know the maximum [Avg] price… per bucket.  Ignore that you would probably do that in a Measure Smile   First, realize that if you throw down a MAX() into a calculated column… it will evaluate over the entire table.  The MAX() function doesn’t care if you have a row context… it is expecting a filter context… and doesn’t find one and so, parties on the whole table.

The other weirdness is that if you call CALCULATE() in a calculated column it will create a filter context, but it will be set to just the current row.   So,  =CALCULATE(MAX(MSFT[Avg])) will…. just return the [Avg] of the current row.   (And remember, without the calculate, it will take the max of the entire table).

But, we want neither of those behaviors.  We want the max of everything “in our bucket”.  Right, I am walking row by row (eg: we have a row context) and saying “what is the max value… constrained to just the buck I am in?”

=CALCULATE(MAX(MSFT[Avg]), FILTER(ALL(MSFT), MSFT[Volume Bucket] = ???))

what goes in place of the ??? part, though?

I mean, based on the title of the article you can probably guess, but let’s look a bit deeper than that Smile

Realize that FILTER() also creates a row context.  Just like evaluating a calculated column creates a row context.  So does SUMX, and some other fun functions.  It makes sense, too.  FILTER() is saying “iterate over things rows…and include them or not, depending on this condition…”  Well, to iterate over rows and make the filtering decision, you need a row context.

image

So, you are walking 1 row at a time from the calculated column madness… then for each of those rows you are walking the table again 1 row at a time to evaluate the FILTER() clause.  The blue column is representing the evaluation of calculated column rows.  Having already evaluated Rows 1-3, we are currently evaluating Row 4.  So, we start evaluating Row 4, and see that the calculation calls FILTER(ALL(MSFT)…) which means, we have to walk every row.  Again.  That is what the weird yellowy-orange color represents.  For Blue 4, I am evaluating Yellow1, Yellow 2, etc… filtering rows in or out before I finally apply my MAX() for Blue 4.

For programming nerds, this is totally like nested for loops….   for i = 1 to 10, for j = 1 to 10.

Okay, so, we have two row contexts.  What does that have to do with the ??? stuff?

=CALCULATE(MAX(MSFT[Avg]), FILTER(ALL(MSFT), MSFT[Volume Bucket] = EARLIER(MSFT[Volume Bucket])))

The EARLIER function is used to say “It is lovely that I have a yellowy-orange row context, but I want to use the blue one!  The previous row context.  The earlier one!”

The Pattern

Pretty much every time you are writing a calculated column and want to say “I want do some maths on values of this table… that are kinda like me…” you will have a calculated column formula that looks very much like the one above.  It is a general pattern worth remembering.

That is not to say you can’t make it more complex.  For example, say you want to calculate the maximum value of the stock in the time window from 6 days ago until today.  You plan to use this in a calculation of “is todays value a new high in the last 60 days?” (which I have to admit, almost sounds useful!).

=CALCULATE(MAX(MSFT[Avg]),
FILTER(
ALL(MSFT),
MSFT[Date] <= EARLIER(MSFT[Date]) && MSFT[Date] > EARLIER(MSFT[Date]) - 60
)
)

This still follows the same pattern, but we use EARLIER() twice so that we can say “is the date I am looking at, while evaluating rows from FILTER… today or before and greater than 60 days ago?   If so, include that rows and take the average of all those rows.  Kinda Cool!

Wrap It Up!

As crazy as this sounds… the EARLIER function is sometimes used… not in a calculated column.  Just… not very often. All that is required is this “nested row context” … say a SUMX (which evaluates some expression against every row in a table, then adds the together) is passed the result of a FILTER() instead of just a table.  Inside that FILTER() you could use EARLIER().  1 row context from SUMX, and a nested row context from the FILTER() operator.  It’s just… I have only actually needed to do this a small handful of times.  Maybe I am just a simple fella Smile

Okay, so there you have our 3rd post on power pivot fundamentals.  As always, feel free to hit me up with questions in the comments, or even recommendation for future posts!

  • About Scott
  • Latest Posts
  • Contact

About Scott Senkeresty

Scott Senkeresty here… Founder and Owner of Tiny Lizard. Yes, I have done some stuff: A Master’s Degree in Computer Science from California Polytechnic State University in beautiful San Luis Obispo, California. Over twelve years developing software for Microsoft, across Office, Windows and Anti-Malware teams… Read More >>

  • The streak is alive! – August 10, 2017
  • DAX KEEPFILTERS Plus Bonus Complaining! – July 20, 2017
  • VAR: The best thing to happen to DAX since CALCULATE() – July 11, 2017
  • Review: Analyzing Data with Power BI and Power Pivot for Excel – June 20, 2017
  • Power BI Date Table – June 2, 2017

View All Posts

Scott Senkeresty here… Founder and Owner of Tiny Lizard. Yes, I have done some stuff: A Master’s Degree in Computer Science from California Polytechnic State University in beautiful San Luis Obispo, California. Over twelve years developing software for Microsoft, across Office, Windows and Anti-Malware teams… Read More >>

Playing with Median Age Data
Update to Power Pivot!

Filed Under: Fundamentals, Power Pivot

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 307 other subscribers

Comments

  1. greg kramer says

    December 30, 2014 at 12:34 pm

    Scott,

    Love how hard you work to make the ideas accessible. Your general helpfulness shines through in all of your posts.

    (will say that just as I was starting to relax at the end of this post, you made me think all the way through the last paragraph whew! 🙂

    Great post.

    Reply
  2. PhilC says

    June 16, 2015 at 2:18 am

    Hi Scott, came across this looking for a solution for a question on http://www.powerpivotforum.com.au. Great worked example on how to use EARLIER. Thanks for taking the time to put this info out there for others.

    Cheers
    Phil

    Reply
  3. Christian says

    April 2, 2016 at 12:55 pm

    Brilliant! Thank you for this great explanation!

    Reply
  4. Karen says

    July 29, 2018 at 10:48 pm

    I was wondering if you could help me understand why when no dates are filtered I get the sum of multiple values instead of just a value for the max(date). The measure seems to work great if I have filtered a date. Here is my code:
    Previous_Value=
    var PreviousDate=CALCULATE(MAX(Table1[Date]),FILTER(Table1,Table1[Id]=EARLIER(Table1[Id]) && Table1[Date]<EARLIER(Table1[Date])))
    return
    LOOKUPVALUE(Table1[Value],Table1[Date],PreviousDate,Table1[Id],Table1[Id])

    Reply
    • Scott Senkeresty says

      July 29, 2018 at 10:56 pm

      Karen, drop me an email. Scott at Tiny Lizard Dot Com.

      We will work it out, but in short “that isn’t what is happening”. LookupValue can’t sum…

      Reply
      • Karen says

        July 29, 2018 at 11:01 pm

        I believe the sum is happening in my table I use to display the Previous_value

        Reply
  5. Sean says

    June 13, 2019 at 12:44 am

    Hey Scott,

    Just saying thank you for your blog post here. Really gave me the best knowledge on how Earlier operates in DAX, than anywhere else I’ve found on the Googles. And that even in 2019, this blog post still roxx!

    Also, greatly appreciate your humor and memeing in your blog posts 🙂

    Toodles,
    Sean P.

    Reply
    • Scott Senkeresty says

      June 13, 2019 at 1:32 am

      Thanks Sean – very kind 🙂

      Reply
  6. Gabriel says

    April 8, 2020 at 5:08 pm

    You’re language is very accessible. I just “get it” when reading this article. Thanks for that!

    Reply
    • Scott Senkeresty says

      April 9, 2020 at 7:20 am

      Thanks Gabriel! I miss writing for the blog on DAX – other stuff just got in the way 🙂 Glad it’s still useful to folks.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Blog Posts

  • The streak is alive! August 10, 2017
  • DAX KEEPFILTERS Plus Bonus Complaining! July 20, 2017
  • VAR: The best thing to happen to DAX since CALCULATE() July 11, 2017
  • Review: Analyzing Data with Power BI and Power Pivot for Excel June 20, 2017
  • Power BI Date Table June 2, 2017

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 307 other subscribers

Copyright © 2025 Tiny Lizard

MENU
  • About
  • Blog
  • Power Pivot
  • Training
  • Contact