cancel
Showing results for
Did you mean:
Helper I

## Difference between consecutive values in a Measure

Good Afternoon,

I need help with an issue regarding the difference between two consecutive values in a Measure.

I've made a measure that allows me to SUM the values of number of operations for each day, you can see the DAX formula here:

Medida= CALCULATE(SUM(Consulta1[Suma Operations]);Consulta1[Operation]="1")

The data looks like:

Date                    Suma Operations                      Operation
09/01/2017                      9                                        1
09/01/2017                      5                                        1

09/01/2017                      8                                        0
09/01/2017                      12                                      0
09/01/2017                      4                                        1
10/01/2017                      9                                        1
10/01/2017                      3                                        0
10/01/2017                      2                                        0
10/01/2017                      1                                        1
11/01/2017                      5                                        1
11/01/2017                      8                                        1
11/01/2017                      3                                        1
12/01/2017                      2                                        1
12/01/2017                      1                                        1
12/01/2017                      4                                        1

What this measures does is to SUM the values of each day if the Operation is equal to 1, so the result that we obtain is (Expressed in a table as it is a measure and it does not appear in the columns):

Date                            Medida
09/01/2017                      18 (9+5+4)
10/01/2017                      10 (9+1)
11/01/2017                      16 (5+8+3)
11/01/2017                      7 (2+1+4)

Ok, now the problem. THe next step is to be able to perform the difference between the consecutive results of the Measure "Medida" and then make a cumulative sum, resulting in:

Date                            Medida                        Difference             Cumulative
09/01/2017                      18 (9+5+4)                   8 (18-10)              8 (8)
10/01/2017                      10 (9+1)                       -6 (10-16)             2 (8-6)

11/01/2017                      16 (5+8+3)                  9 (16-7)                 11 (2+9)

11/01/2017                      7 (2+1+4)

The problem that I found is that I cannot use the formula NEXTDAY in order to compute the difference as then the Cumulative Formula does not work well.

Can someone help me? Thanks

1 ACCEPTED SOLUTION
Super User

@SSS

Try this

```Cumulative=
IF (
HASONEVALUE ( Consulta1[Date] ),
SUMX (
FILTER (
ALL ( Consulta1[Date] ),
Consulta1[Date] <= VALUES ( Consulta1[Date] )
&& MONTH ( Consulta1[Date] ) = MONTH ( VALUES ( Consulta1[Date] ) )
),
[Difference]
)
)```

Regards
Zubair

Please try my custom visuals
8 REPLIES 8
Microsoft

@SSS,

Create a new table using DAX below.

`Table = SUMMARIZE(Consulta1,Consulta1[Date],"Medida",[Medida])`

In the new table, create Index column, Difference column and Cumulative measure.

`Index = CALCULATE(COUNT('Table'[Date]),ALL('Table'),'Table'[Date]<=EARLIER('Table'[Date]))`
`Difference = var previous=CALCULATE(FIRSTNONBLANK('Table'[Medida],'Table'[Medida]),FILTER('Table','Table'[Index]-1=EARLIER('Table'[Index]) )) return IF(ISBLANK(previous),BLANK(),'Table'[Medida]-previous)`
`Cumulative = IF(SUM('Table'[Difference])=BLANK(),BLANK(),CALCULATE(SUM('Table'[Difference]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date]))))`

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

@SSS

Try these measures

First Create this Measure

```Difference =
VAR Followingday =
NEXTDAY ( Consulta1[Date] )
RETURN
[Medida]
- CALCULATE (
[Medida],
FILTER ( ALL ( Consulta1 ), Consulta1[Date] = Followingday )
)```

Regards
Zubair

Please try my custom visuals
Super User

@SSS

Now create this one

```Cumulative=
IF (
HASONEVALUE ( Consulta1[Date] ),
SUMX (
FILTER (
ALL ( Consulta1[Date] ),
Consulta1[Date] <= VALUES ( Consulta1[Date] )
),
[Difference]
)
)```

Regards
Zubair

Please try my custom visuals
Helper I

Could I ask if it is possible to add one last issue? I would like the cumulative sum to reset each month, it is possible?

Thanks!

Super User

@SSS

Do you want to reset both Difference and Cumulative?

Regards
Zubair

Please try my custom visuals
Helper I

Just the Cumulative!!

Super User

@SSS

Try this

```Cumulative=
IF (
HASONEVALUE ( Consulta1[Date] ),
SUMX (
FILTER (
ALL ( Consulta1[Date] ),
Consulta1[Date] <= VALUES ( Consulta1[Date] )
&& MONTH ( Consulta1[Date] ) = MONTH ( VALUES ( Consulta1[Date] ) )
),
[Difference]
)
)```

Regards
Zubair

Please try my custom visuals
Helper I

Thanks, I've tried your solution and it totally works!!!

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors