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  Community Champion

@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

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.  Community Champion

@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  Community Champion

@SSS

Now create this one

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

Regards
Zubair  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!  Community Champion

@SSS

Do you want to reset both Difference and Cumulative?

Regards
Zubair  Helper I

Just the Cumulative!!  Community Champion

@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  Helper I  