Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
How to find the difference between the current row and next row value of tha sales column.
Input data:
Name | Term | sales |
HA | 0 | 2.4137 |
HA | 1 | 2.4019 |
HA | 2 | 2.4024 |
HA | 3 | 2.4097 |
HA | 4 | 2.4138 |
HA | 5 | 2.4134 |
HA | 6 | 2.4094 |
HA | 7 | 2.409 |
HA | 8 | 2.4034 |
HA | 9 | 2.3911 |
HA | 10 | 2.372 |
BB | 0 | 2.3614 |
BB | 1 | 2.3532 |
BB | 2 | 2.348 |
expected output:
Name | Term | sales | Difference |
HA | 0 | 2.4137 | 0.0118 |
HA | 1 | 2.4019 | -0.0005 |
HA | 2 | 2.4024 | -0.0073 |
HA | 3 | 2.4097 | -0.0041 |
HA | 4 | 2.4138 | 0.0004 |
HA | 5 | 2.4134 | 0.004 |
HA | 6 | 2.4094 | 0.0004 |
HA | 7 | 2.409 | 0.0056 |
HA | 8 | 2.4034 | 0.0123 |
HA | 9 | 2.3911 | 0.0191 |
HA | 10 | 2.372 | 0 |
BB | 0 | 2.3614 | 0.0082 |
BB | 1 | 2.3532 | 0.0052 |
BB | 2 | 2.348 | 0 |
Thanks,
SBC
Solved! Go to Solution.
@SBC
Please refer to attached sample file with the proposed solution
Difference =
VAR CurrentTerm = 'Table'[Term]
VAR CurrentSales = 'Table'[Sales]
VAR CurrentNameTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Name] ) )
VAR TableAfter = FILTER ( CurrentNameTable, 'Table'[Term] > CurrentTerm )
VAR NextRecord = TOPN ( 1, TableAfter, 'Table'[Term], ASC )
VAR NextSales = MAXX ( NextRecord, 'Table'[Sales] )
RETURN
CurrentSales - COALESCE ( NextSales, CurrentSales )
Hi @SBC
please try
Difference =
IF (
HASONEVALUE ( 'Table'[Term] ),
SUMX (
SUMMARIZE ( 'Table', 'Table'[Name], 'Table'[Term] ),
VAR CurrentTerm = 'Table'[Term]
VAR CurrentSales =
CALCULATE ( SUM ( 'Table'[Sales] ) )
VAR T1 =
CALCULATETABLE ( 'Table', ALL ( 'Table'[Term] ) )
VAR T2 =
FILTER ( T1, 'Table'[Term] > CurrentTerm )
VAR T3 =
TOPN ( 1, T2, 'Table'[Term], ASC )
VAR NextSales =
SUMX ( T3, 'Table'[Sales] )
RETURN
CurrentSales - COALESCE ( NextSales, CurrentSales )
)
)
Hi @tamerj1 ,
Thanks for your response, i have applied measure which you have provided but it not working as expected, showing all values as "0"
expected output:
Name | Term | sales | Difference |
HA | 0 | 2.4137 | 0.0118 |
HA | 1 | 2.4019 | -0.0005 |
HA | 2 | 2.4024 | -0.0073 |
HA | 3 | 2.4097 | -0.0041 |
HA | 4 | 2.4138 | 0.0004 |
HA | 5 | 2.4134 | 0.004 |
HA | 6 | 2.4094 | 0.0004 |
HA | 7 | 2.409 | 0.0056 |
HA | 8 | 2.4034 | 0.0123 |
HA | 9 | 2.3911 | 0.0191 |
HA | 10 | 2.372 | 0 |
BB | 0 | 2.3614 | 0.0082 |
BB | 1 | 2.3532 | 0.0052 |
BB | 2 | 2.348 | 0 |
Thanks,
SBC
@SBC
Sales is a measure or a column in the source table? Accordingly Difference should be a measure or a calculated column? Can you provide a sample of the source data just to see how it looks like and what are the granularity of different columns?
@tamerj1 ,
Sales is a column from the source table.Name,Term,Sales are from source we need to show the difference for each sales of term, last name difference should be zero.
Please check below expected output
Input data:
Name | Term | sales |
HA | 0 | 2.4137 |
HA | 1 | 2.4019 |
HA | 2 | 2.4024 |
HA | 3 | 2.4097 |
HA | 4 | 2.4138 |
HA | 5 | 2.4134 |
HA | 6 | 2.4094 |
HA | 7 | 2.409 |
HA | 8 | 2.4034 |
HA | 9 | 2.3911 |
HA | 10 | 2.372 |
BB | 0 | 2.3614 |
BB | 1 | 2.3532 |
BB | 2 | 2.348 |
expected output:
Name | Term | sales | Difference |
HA | 0 | 2.4137 | 0.0118 |
HA | 1 | 2.4019 | -0.0005 |
HA | 2 | 2.4024 | -0.0073 |
HA | 3 | 2.4097 | -0.0041 |
HA | 4 | 2.4138 | 0.0004 |
HA | 5 | 2.4134 | 0.004 |
HA | 6 | 2.4094 | 0.0004 |
HA | 7 | 2.409 | 0.0056 |
HA | 8 | 2.4034 | 0.0123 |
HA | 9 | 2.3911 | 0.0191 |
HA | 10 | 2.372 | 0 |
BB | 0 | 2.3614 | 0.0082 |
BB | 1 | 2.3532 | 0.0052 |
BB | 2 | 2.348 | 0 |
Thanks,
SBC
@SBC
Please refer to attached sample file with the proposed solution
Difference =
VAR CurrentTerm = 'Table'[Term]
VAR CurrentSales = 'Table'[Sales]
VAR CurrentNameTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Name] ) )
VAR TableAfter = FILTER ( CurrentNameTable, 'Table'[Term] > CurrentTerm )
VAR NextRecord = TOPN ( 1, TableAfter, 'Table'[Term], ASC )
VAR NextSales = MAXX ( NextRecord, 'Table'[Sales] )
RETURN
CurrentSales - COALESCE ( NextSales, CurrentSales )
Hi @SBC ,
Please try the following measure:
Difference =
CALCULATE(
SUM( 'Table'[sales] )
- CALCULATE(
SUM('Table'[sales]),
OFFSET(
1,
,
ORDERBY( 'Table'[Term], ASC )
)
)
)
Here is the result:
The OFFSET function is relatively new, you may need to update your Power BI Desktop to use it.
If I answered your question, please mark my post as a solution.
Best,
Hi @Alf94 ,
Thanks for your response we need output as 0 for the last Name column of term.iam using latest version of power bi.
expected output:
Name | Term | sales | Difference |
HA | 0 | 2.4137 | 0.0118 |
HA | 1 | 2.4019 | -0.0005 |
HA | 2 | 2.4024 | -0.0073 |
HA | 3 | 2.4097 | -0.0041 |
HA | 4 | 2.4138 | 0.0004 |
HA | 5 | 2.4134 | 0.004 |
HA | 6 | 2.4094 | 0.0004 |
HA | 7 | 2.409 | 0.0056 |
HA | 8 | 2.4034 | 0.0123 |
HA | 9 | 2.3911 | 0.0191 |
HA | 10 | 2.372 | 0 |
BB | 0 | 2.3614 | 0.0082 |
BB | 1 | 2.3532 | 0.0052 |
BB | 2 | 2.348 | 0 |
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
7 |