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’.
Your friend Annie.