Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
SBC
Helper II
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
HA02.4137
HA12.4019
HA22.4024
HA32.4097
HA42.4138
HA52.4134
HA62.4094
HA72.409
HA82.4034
HA92.3911
HA102.372
BB02.3614
BB12.3532
BB22.348

 

expected output:

 

Name     Term             sales                   Difference
HA02.41370.0118
HA12.4019-0.0005
HA22.4024-0.0073
HA32.4097-0.0041
HA42.41380.0004
HA52.41340.004
HA62.40940.0004
HA72.4090.0056
HA82.40340.0123
HA92.39110.0191
HA102.3720
BB02.36140.0082
BB12.35320.0052
BB22.3480



Thanks,

SBC

1 ACCEPTED SOLUTION

@SBC 
Please refer to attached sample file with the proposed solution

1.png

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 )

View solution in original post

7 REPLIES 7
tamerj1
Super User
Super User

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"

SBC_0-1686198224563.png

expected output:

 

Name     Term             sales                   Difference
HA02.41370.0118
HA12.4019-0.0005
HA22.4024-0.0073
HA32.4097-0.0041
HA42.41380.0004
HA52.41340.004
HA62.40940.0004
HA72.4090.0056
HA82.40340.0123
HA92.39110.0191
HA102.3720
BB02.36140.0082
BB12.35320.0052
BB22.3480

 

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
HA02.4137
HA12.4019
HA22.4024
HA32.4097
HA42.4138
HA52.4134
HA62.4094
HA72.409
HA82.4034
HA92.3911
HA102.372
BB02.3614
BB12.3532
BB22.348

expected output:

 

Name     Term             sales                   Difference
HA02.41370.0118
HA12.4019-0.0005
HA22.4024-0.0073
HA32.4097-0.0041
HA42.41380.0004
HA52.41340.004
HA62.40940.0004
HA72.4090.0056
HA82.40340.0123
HA92.39110.0191
HA102.3720
BB02.36140.0082
BB12.35320.0052
BB22.3480

 

 

Thanks,

SBC

 

@SBC 
Please refer to attached sample file with the proposed solution

1.png

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 )
Alf94
Super User
Super User

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:

 

Alf94_0-1686154056595.png

 

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
HA02.41370.0118
HA12.4019-0.0005
HA22.4024-0.0073
HA32.4097-0.0041
HA42.41380.0004
HA52.41340.004
HA62.40940.0004
HA72.4090.0056
HA82.40340.0123
HA92.39110.0191
HA102.3720
BB02.36140.0082
BB12.35320.0052
BB22.3480

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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

Top Solution Authors