Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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,
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
Try these measures
First Create this Measure
Difference = VAR Followingday = NEXTDAY ( Consulta1[Date] ) RETURN [Medida] - CALCULATE ( [Medida], FILTER ( ALL ( Consulta1 ), Consulta1[Date] = Followingday ) )
Now create this one
Cumulative= IF ( HASONEVALUE ( Consulta1[Date] ), SUMX ( FILTER ( ALL ( Consulta1[Date] ), Consulta1[Date] <= VALUES ( Consulta1[Date] ) ), [Difference] ) )
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!
Do you want to reset both Difference and Cumulative?
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] ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |