cancel
Showing results for
Did you mean:
Member

Hello All,

How do you perform lead and lag functions in DAX?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: Compute Lead and Lag

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

Proud to be a Datanaut!

Highlighted
Super User

## Re: Compute Lead and Lag

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