The DAX EARLIER function is often explained as – “It returns the current row value of a column” Which is correct.. but partially! The simplistic definition above does not fully explain the nitty gritties of EARLIER. In this post, I’ll do my best to show you the finer workings of EARLIER. We’ll also take a look some use cases and how to use EARLIER in a Column and in a Measure. You’re going to stay here for a bit so get a coffee before you start 🙂   I would highly recommend to use Variables (VAR) instead of the Earlier Function.    This is tricky, wanna grab the Video first ? EARLIER Syntax The function accepts 2 inputs Column Name Number which is optional Now at the risk of losing you at the start I am going to say something difficult but if you stick around you’d understand every word by the end of this post.  The Earlier Function returns the value of the Current Row in the Column specified and the EARLIER function at-least needs to have 2 nested ROW Contexts. If you din’t understand anything.. that’s okay, you should just remember 2 things and move on. Earlier will return the value of the current row in a column. To use the Earlier we’ll need to create 2 nested row contexts – and that is the most unexplained part.   Consider this Sales Table Question – Create a Column to count the instance for each date, in other words a COUNTIF on Date Column So I’d write 2 since 2nd-Jan-2011 appears twice And number 3 for 6th-Jan-2011.. you get the idea! Let’s solve this using EARLIER   Using EARLIER in a Column If start your formula like this = EARLIER ( Sales[Date] ) you’d won’t be able to write the formula since that is deemed invalid. So let’s start differently – consider the following calculated column COUNTIF Dates Column = COUNTROWS( FILTER( Sales, EARLIER(Sales[Date]) = Sales[Date] ) ) Solved. But the question that has personally bothered me for a long time is that.. Why do I need the FILTER Function to be able to work with EARLIER and Why din’t the EARLIER function work when written at the start of the calculated column ? Remember what I said about EARLIER – It needs 2 nested ROW Contexts. Time to explain this   Step 1 – When you created a Calculated Column in the Sales table you are technically working in a ROW Context and are writing the formula in each row of that Table. Step 2 – When you use the FILTER function in each row of the SalesTable, a nested second row context is created that pulls the entire Sales table (again) in each row of the outside Sales Table Step 3 – Each Date in the second row context (inner Sales Table loop, which is inside the FILTER function) is then compared with the current row Date of the first filter context (outer Sales Table loop). So EARLIER in essence means the current row of the immediate outer loop. and that’s how the earlier function works.   Using EARLIER in a Measure To be able to meaningfully explain the use case of EARLIER in a Measure consider this Case. Along with the sales table I have a Region Expenses Table. Which has a one to many relationship with the Sales Table. Sales being on the many side, linked with Region Column. I have to allocate the Budget Value equally to all the rows of the respective region in the Sales table. For instance, if I filter my sales table for Region = “New Delhi”, I get 476 rows, hence the allocation will be = 20,000/476 for each row of the Sales table. Question – Create a Measure that allocates the Budget Value in the Sales Table. Consider this Measure Budget Allocated Measure using Earlier = SUMX( Sales, 1 / COUNTROWS( FILTER( ALL(Sales), EARLIER(Sales[Region]) = Sales[Region] ) ) * RELATED(‘Region Expenses'[Budget]) ) My logic will be somewhat like this In each row of the Sales Table Find the allocation % for each row i.e. 1/476 (eg. for New Delhi) Then multiply that allocation % with the Budget Value i.e. 20,000 x 1 / 476 (for New Delhi) And since to be able to work with the EARLIER Function I need to have 2 nested ROW Contexts, I start by using SUMX and then use FILTER to find the count of total rows that belong to each Region. An alternative approach (measure) using Variables could be like this Budget Allocated Measure using Variables = SUMX( Sales, VAR CurrentRegion = Sales[Region] RETURN 1 / COUNTROWS( FILTER( ALL(Sales), CurrentRegion = Sales[Region] ) ) * RELATED(‘Region Expenses'[Budget]) )   Another measure to do the same thing using could be done using ALLEXCEPT Budget Allocated Measure using AllExcept = SUMX( Sales, DIVIDE( 1, COUNTROWS( CALCULATETABLE( Sales, ALLEXCEPT(Sales,’Region Expenses'[Region]) ) ) ) * RELATED(‘Region Expenses'[Budget]) ) All doing the same thing 🙂 See the Results   Using EARLIER in more than 2 nested Row Contexts Remember at the start of this post, I mentioned that earlier has 2 parts.. . Column Name Number – What is that for? Let’s Explore Consider that I added 2 more tables with no relationships to any other tables First Table Value A B Second Table Value 1 2 Notice the column name (Value) is the same in both the above tables and to induce further ambiguity, I’ll add another column to my Sales Table called “Value”. In this column I’ll write my name throughout all the rows. I then create a test column and write the following DAX Test = CONCATENATEX( ‘First Table’, CONCATENATEX( ‘Second Table’, EARLIER([Value], 2) & “-” & [Value] ) ) Now notice the results A couple of interesting (or mundane) things to note.. The number 2 allows you go outside 2 loops and fetch the current row value of the outer most Sales Table. Since there are only 2 nested loops, a number … Continue reading Understanding Earlier Function in DAX