cancel
Showing results for
Did you mean:
Frequent Visitor

## Subtract one row from the prior

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.

1 ACCEPTED SOLUTION
Resident Rockstar

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.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Resident Rockstar

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.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Thank you very much!

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors