Compute Lead and Lag

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

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