Forum Discussion
EARLIER FUNCTION
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.
Community ChampionAnishPNair 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 .
- Greg_Deckler
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.
Thanks Greg_Deckler . It is clear now. However if you can explain what current context means, it would be helpful .
