Forum Discussion
EARLIER FUNCTION
Hi Greg_Deckler or Anyone,
I started to learn Power BI using Learn Power BI Second Edition book and the Earlier Function was used in Ch 5 . I am not able to understand the Earlier function at all. I went through the docs ( https://learn.microsoft.com/en-us/dax/earlier-function-dax) and i still didn't follow . It mentions outer evaluation pass of the mentioned column . What does "outer evaluation pass of the mentioned column" mean ? Can someone explain the function in simple layman language .
AnishPNair Perhaps an image will help:
So let's just focus on this piece of the code:
ADDCOLUMNS( __Table, "__Inventory", __Inventory - SUMX(FILTER(__Table,[Date]<=EARLIER([Date])),[Value]) )The ADDCOLUMNS function takes a table and adds a calculated column. In this case our starting table just has Date and Value columns in it and we are adding a column called __Inventory. This is the main table shown in the image and for each row it is going to execute the forumula:
__Inventory - SUMX(FILTER(__Table,[Date]<=EARLIER([Date])),[Value])Within this code is a FILTER statement. This FILTER statement creates a "current context" for the SUMX function. The "earlier" context is the row context in which the __Inventory column is being calculated for the current row, in this case I chose January 3rd, 2022 but remember that every row in the table is getting the same calculation performed. Thus, when the SUMX(FILTER... statement executes for the January 3 2022 row, using EARLIER refers to this row context and thus EALIER([Date]) returns January 3 2022, the value for the current row being iterated on and thus the FILTER statement forming the current context for the SUMX function returns the table highlighted in orange on the right of the image. Thus, when summing up the Value column within this context, it would be 20 + 40 + 30 or 90 and thus return 2000 - 90 = 1910 for the January 3 2022 row.
9 Replies
Hi AnishPNair ,
with the introduction of variables in DAX, the usage of the (not so easy to understand) EARLIER function has become obsolete in most of the cases. This might help: EARLIER – DAX Guide
My understanding of EARLIER is that it references the outer row context of the current evaluation context. So an understanding of DAXs evaluation contexts is needed to get a hang of it at all.Hi AnishPNair ,
maybe these will get you started:
Understanding Evaluation Context in DAX - BI Gorilla
Microsoft Power BI: Deep dive into DAX evaluation context - BRK3060 - YouTubeit's not easy, but one of the basic pillars of the DAX-language.
Thanks ImkeF . I will take a look . Please bear with me in case i have more questions.
Hi ImkeF
Thank you for the response. I am still clueless . I went through the link and not able to understand . Is there anything that can help me understand DAXs evaluation contexts. Thanks
AnishPNair Thanks ImkeF, great stuff. Consider that these are equivalent statements:
Days of Supply = VAR __Inventory = 2000 VAR __Date = TODAY() VAR __Table = FILTER(ALLSELECTED('Table'),[Date]>=__Date) VAR __Table1 = ADDCOLUMNS( __Table, "__Inventory", __Inventory - SUMX(FILTER(__Table,[Date]<=EARLIER([Date])),[Value]) ) VAR __EndDate = MAXX(FILTER(__Table1,[__Inventory]>=0),[Date]) RETURN (__EndDate - __Date) * 1. Days of Supply sans EARLIER = VAR __Inventory = 2000 VAR __Date = TODAY() VAR __Table = FILTER(ALLSELECTED('Table'),[Date]>=__Date) VAR __Table1 = ADDCOLUMNS( __Table, "__Inventory", VAR __CurrentDate = [Date] RETURN __Inventory - SUMX(FILTER(__Table,[Date]<=__CurrentDate),[Value]) ) VAR __EndDate = MAXX(FILTER(__Table1,[__Inventory]>=0),[Date]) RETURN (__EndDate - __Date) * 1.I am also attaching the PBIX for additional reference. Basically using EARLIER is the same as creating a VAR outside of the current function essentially and using that variable within the function.
@Greg_Deckler Thanks for replying to the post. However i am still not able to understand as i am not familiar with variables in DAX. Is there a simpler way as i just started reading your book .
AnishPNair Perhaps an image will help:
So let's just focus on this piece of the code:
ADDCOLUMNS( __Table, "__Inventory", __Inventory - SUMX(FILTER(__Table,[Date]<=EARLIER([Date])),[Value]) )The ADDCOLUMNS function takes a table and adds a calculated column. In this case our starting table just has Date and Value columns in it and we are adding a column called __Inventory. This is the main table shown in the image and for each row it is going to execute the forumula:
__Inventory - SUMX(FILTER(__Table,[Date]<=EARLIER([Date])),[Value])Within this code is a FILTER statement. This FILTER statement creates a "current context" for the SUMX function. The "earlier" context is the row context in which the __Inventory column is being calculated for the current row, in this case I chose January 3rd, 2022 but remember that every row in the table is getting the same calculation performed. Thus, when the SUMX(FILTER... statement executes for the January 3 2022 row, using EARLIER refers to this row context and thus EALIER([Date]) returns January 3 2022, the value for the current row being iterated on and thus the FILTER statement forming the current context for the SUMX function returns the table highlighted in orange on the right of the image. Thus, when summing up the Value column within this context, it would be 20 + 40 + 30 or 90 and thus return 2000 - 90 = 1910 for the January 3 2022 row.
