cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SSS
Helper I
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

@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

View solution in original post

8 REPLIES 8
v-yuezhe-msft
Microsoft
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]))))

1.JPG



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.
Zubair_Muhammad
Super User
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

@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

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!

@SSS

 

Do you want to reset both Difference and Cumulative?


Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad,

 

Just the Cumulative!!

@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

@Zubair_Muhammad

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

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

Top Solution Authors
Top Kudoed Authors