Difference between consecutive values in a Measure

[SSS]

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

[v-yuezhe-msft]

@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

[Zubair_Muhammad]

@SSS

Try these measures

First Create this Measure

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

[Zubair_Muhammad]

@SSS

Now create this one

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

[SSS]

Thanks @Zubair_Muhammad, your solution works!!

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!

[Zubair_Muhammad]

@SSS

Do you want to reset both Difference and Cumulative?

[SSS]

Hi @Zubair_Muhammad,

Just the Cumulative!!

[Zubair_Muhammad · Accepted solution]

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

[SSS]

@Zubair_Muhammad

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