Subtract one row from the prior

[leylarm]

Hello

I have 3 columns :

Index  (1,2,3,4,5,..,365)

Days (1May, 2 May, ..., Apr 30) - 365days

Month(May2017, May 2017, ,..., April 2018) - 365

Numbers (5454,2187,15487,48797,...,26554)

I want the find the difference, like (in index)

1-1= 0

2-1=2187-5454

3-2= 15487-2187

and so on

i found a Dax script:

Diff =
VAR Index = 'Table'[Index]
VAR Reference = 'Table'[Month].[Day]
VAR PrevCreditP =
CALCULATE (
FIRSTNONBLANK ( 'Table'[Numbers], TRUE () ),
FILTER ( 'Table', 'Table'[Index] = Index - 1 && 'Table'[Month].[Day] = Reference )
)
RETURN
IF (
ISBLANK ( PrevCreditP ),
BLANK (),
'Table'[Credit Provision] - PrevCreditP
)

But it works not properly. Despite the correct numbers in each columns ( in power bi and excel), their sum - are different.

Kindly ask to help me

[v-piga-msft · Accepted solution]

Hi @leylarm,

Please create a calculated column with the formula below.

diff =
VAR current_index = 'Table'[Index]
VAR prior =
    CALCULATE (
        SUM ( 'Table'[Numbers] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = current_index - 1 )
    )
RETURN
    'Table'[Numbers] - IF ( ISBLANK ( prior ), 'Table'[Numbers], prior )

Or measure with the foemula below.

Measure =
VAR current_index =
    MAX ( 'Table'[Index] )
VAR prior =
    CALCULATE (
        SUM ( 'Table'[Numbers] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = current_index - 1 )
    )
RETURN
    MAX ( 'Table'[Numbers] )
        - IF ( ISBLANK ( prior ), MAX ( 'Table'[Numbers] ), prior )

Here is the result.

In addition, you could refer to the similar thread.

Best Regards,

Cherry

[leylarm]

Thank you very much!