Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |