Forum Discussion

SSS's avatar
SSS
Helper I
8 years ago
Solved

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

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

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

8 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      SSS

       

      Now create this one

       

      Cumulative=
IF (
    HASONEVALUE ( Consulta1[Date] ),
    SUMX (
        FILTER (
            ALL ( Consulta1[Date] ),
            Consulta1[Date] <= VALUES ( Consulta1[Date] )
        ),
        [Difference]
    )
)
      • SSS's avatar
        SSS
        Helper I
        8 years ago

        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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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