In SQL, we have two window functions call lead and lag, and with these two functions, you can get the previous and next value of a column partition by and order by other columns in a table.
Use our Advanturework Sales.SalesOrderhead table as an example. The following code can give you the previous and next SalesOrderID for a SalesPersion order by OrderDate.
However, it is a very expensive function because the SQL engine need to fetch through the entire table for every row calculation where the functions are called.
It is much faster to use DAX in SSAS tabular model in this case, where the column-store and vertipaq compression technologies are embedded. To use DAX replace lead and lag function, we will be using a key function in DAX called ‘Earlier’.
Using the same example mentioned above,
You can write Previous Order ID calculate column like this:
As we know, ‘Calculate’ Function covers the current row context to filter context of the calculation (as the first argument) inside ‘Calculate’. However, the filter contexts (second and the following arguments of ‘Calculate’) created in side ‘Calculate’ block those external filter contexts when they are referring the same columns. In this case, ‘Filter’ function blocked all the filter contexts added externally on the ‘SalesOrderHeader’ table, in other word, the calculation in the first argument of ‘Calculate’ MAX(SalesOrderHeader[SalesOrderID]) don’t know which row it is at SalesOrderHeader table. Only the ‘Earlier’ function brings the previous filter contexts back, which allows MAX(SalesOrderHeader[SalesOrderID]) aware of which row it is at.
Using the second row of the above screenshot as an example, the DAX calculation of PreviousOrderID column can be explained as:
Find the max SalesOrderID where SalesPersonID equal to the SalesPersionID of existing row (called by ‘Earlier’ function) which is ‘274’ and OrderDate are older than the OrderDate of existing row (called by ‘Earlier’ function) which are all the records with OrderDate older than 9/1/2015. Thus, the result is ‘43846’.
Thanks.
Your friend Annie.
Gal Love Data 
Thanks for this post on Lead/Lag windowing functions using DAX functions Annie.
In the DAX results though as per your example, the Previous/Next values are not correct and the filter would have limitations when the “OrderDate” field has the same value as the previous record.
Any idea how to get around this in DAX?
LikeLike
Hi James,
Thanks for reading through my post. I see your point on the OrderDate field has the same value as previous records. In this situation, we will need to have another column to distinguish the duplicate. The idea is that use/create a ranked column to distinguish the record when the OrderDate is the same, it can be Orderid if the Orderid is incrementally added to the transaction table or it can be OrderDate + OrderDateTime. Then, replace the new ranked column to the ‘OrderDate’ in the DAX function. Let me know if it works for your situation.
LikeLiked by 1 person