cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper II

## Find the difference between rows and next row in power bi

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

1 ACCEPTED SOLUTION
Super User

@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 )``````
7 REPLIES 7
Super User

Hi @SBC

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 )
)
)

Helper II

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

Super User

@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?

Helper II

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.

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

Super User

@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 )``````
Super User

Hi @SBC ,

``````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.

Best,

Helper II

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors