topic Re: Find the difference between rows and next row in power bi in DAX Commands and Tips

Find the difference between rows and next row in power bi

SBC — Wed, 07 Jun 2023 16:10:22 GMT

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

Re: Find the difference between rows and next row in power bi

Alf94 — Wed, 07 Jun 2023 16:11:06 GMT

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,

Re: Find the difference between rows and next row in power bi

SBC — Wed, 07 Jun 2023 16:31:27 GMT

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

Re: Find the difference between rows and next row in power bi

tamerj1 — Wed, 07 Jun 2023 18:41:17 GMT

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

Re: Find the difference between rows and next row in power bi

SBC — Thu, 08 Jun 2023 04:24:55 GMT

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

Re: Find the difference between rows and next row in power bi

tamerj1 — Thu, 08 Jun 2023 06:52:25 GMT

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?

Re: Find the difference between rows and next row in power bi

SBC — Thu, 08 Jun 2023 07:05:14 GMT

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

Re: Find the difference between rows and next row in power bi

tamerj1 — Thu, 08 Jun 2023 07:36:16 GMT

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 )