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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

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

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!