Compute Lead and Lag

[Anonymous]

Hello All,

How do you perform lead and lag functions in DAX?

Thanks

Aditya

[MFelix · Accepted solution]

Hi @Anonymous,

The DAX calculations are based on context so if you want to perform calculations that are based on the previous records or next records you have several ways of doing this from the EARLIER function, the time intelligence functions, the usage of ID column.

I have setup a simple model with tabel below:

ID             ENAME     JOB               SAL

1	MILLER	CLERK	1300
2	CLARK	MANAGER	2450
3	KING	PRESIDENT	5000
4	SMITH	CLERK	800
5	ADAMS	CLERK	1100
6	JONES	MANAGER	2975
7	SCOTT	ANALYST	3000
8	FORD	ANALYST	3000
9	JAMES	CLERK	950
10	MARTIN	SALESMAN	1250
11	WARD	SALESMAN	1250
12	TURNER	SALESMAN	1500
13	ALLEN	SALESMAN	1600
14	BLAKE	MANAGER	2850
15	MARTIN	SALESMAN	1250
16	WARD	SALESMAN	1250
17	TURNER	SALESMAN	1500
18	ALLEN	SALESMAN	1600
19	BLAKE	MANAGER	2850

If you add this two measure you will get the calculation with previous row and next row however this is just an example, I did it with a measure but you can also do it in the query editor making it like SQL with an addtinal column. So you have several ways of making LEAD and LAG calculations depending on your model and necessities.

Previous Difference =
VAR Selecet_Emp =
    ( MAX ( Salary[ID] ) - 1 )
RETURN
    IF (
        HASONEFILTER ( Salary[ID] );
        SUM ( Salary[SAL] )
            - CALCULATE (
                SUM ( Salary[SAL] );
                FILTER (
                    ALL ( Salary[ID]; Salary[ENAME]; Salary[JOB] );
                    Salary[ID] = Selecet_Emp
                )
            );
        BLANK ()
    )




Next Difference = 
VAR Selecet_Emp =
    ( MAX ( Salary[ID] ) + 1 )
RETURN
    IF (
        HASONEFILTER ( Salary[ID] );
        SUM ( Salary[SAL] )
            - CALCULATE (
                SUM ( Salary[SAL] );
                FILTER (
                    ALL ( Salary[ID]; Salary[ENAME]; Salary[JOB] );
                    Salary[ID] = Selecet_Emp
                )
            );
        BLANK ()
    )

Regards,

MFelix