cancel
Showing results for
Did you mean:
Member

## Compute Lead and Lag

Hello All,

How do you perform lead and lag functions in DAX?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
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

Regards

MFelix

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

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

Regards

MFelix

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Announcements

#### New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)