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

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

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!

Zubair_Muhammad
Community Champion
Community Champion

@SSS

 

Do you want to reset both Difference and Cumulative?


Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad,

 

Just the Cumulative!!

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

Please try my custom visuals

View solution in original post

@Zubair_Muhammad

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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors