Forum Discussion
Helper II Need a Measure not a Power Query Formula - Accumulative column
Dear Team
suppose we work in sales, every month there is a target, for one of the Month, if you exceed the target, then i need to save this increase in Accumulative column. lets take example
**************************
1 jan , Sales=103904, Target = 108371, so Diff = Sales - Target. so Diff = 103904-108371=-4467, if Diff >0 then we need to save The Value in Accumulative. but if the value less than 0, i need to subtract the value from previous month accumulative, since this is the first month, i need to add 0 to accumulative column. not negative value, to show that my saving amount is 0.
so accumulative value for Jan = 0.
now lets see Feb
****************
1 Feb, Sales , Sales=184515, Target = 101049, so Diff = Sales - Target. so Diff = 83466, if Diff >0 then we need to save The Value in Accumulative. but if the value less than 0, i need to add 0 to accumulative column.
so accumulative value for Feb= Jan accumulative + Feb Increase = 0 + 83466, so Feb Accumulative = 83466.
****************
i have the answer from one of the member in power Query, but is it possible to create the Accumulative column in a DAX Measure?
Regards
Maher
Hi mhrkhader01 ,
You can try the next option:
Create these measures:
#Accum = VAR currentDate = SELECTEDVALUE ( T[Date] ) VAR sumAmt = CALCULATE ( SUM ( T[Diff] ), FILTER ( ALLSELECTED ( T ), T[Date] <= currentDate ) ) RETURN sumAmt#AccumFiltered = VAR currentDate = SELECTEDVALUE ( T[Date] ) VAR firstDatValue = MINX ( ALLSELECTED ( T ), T[Date] ) VAR minValue = MINX ( FILTER ( T, T[Date] = firstDatValue ), T[Diff] ) VAR sumAmt = CALCULATE ( SUM ( T[Diff] ), FILTER ( ALLSELECTED ( T ), T[Date] <= currentDate ) ) VAR minOfSum = MIN ( 0, MINX ( FILTER ( ALLSELECTED ( T ), T[Date] <= currentDate ), [Accum] ) ) RETURN IF ( currentDate = firstDatValue && minValue < 0, sumAmt - minValue, sumAmt - minOfSum )If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
You can use MAX instead of SELECTEDVALUE .
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
8 Replies
- amitchandak
Super User
mhrkhader01 , One of the way to get cumulative formula is
calculate(sum(Table[diff]), filter(allselected(Table), Table[Date] <=max(Table[Date])))
Other are like MTD and YTD you can using time intelligence
MTD= CALCULATE(sum(Table[diff]),DATESMTD('Date'[Date]))
YTD= CALCULATE(sum(Table[diff]),DATESYTD('Date'[Date],"12/31"))
mhrkhader01Dear amit,
i tried this but the issue is that if the value for current month accumulative is 0 then i need the measure to be calculated from next month, the issue forthe abov measures that are calculate all previous rows values for the difference, i need the difference to be cacluated from the row i stopped.
look to row number 7, the above measures you mensioned will calculate all values from row number 1 till 7 for Diff column. but what suppose to calculate is the sum of row number 6 & 7 only. why? because from row number 7, row number 5 for accumulative is 0 so i shouild start from row number 6 and calculate till 7 for accumulatove for row number 7.
- ERD
Community Champion
Hi mhrkhader01 ,
You can try the next option:
Create these measures:
#Accum = VAR currentDate = SELECTEDVALUE ( T[Date] ) VAR sumAmt = CALCULATE ( SUM ( T[Diff] ), FILTER ( ALLSELECTED ( T ), T[Date] <= currentDate ) ) RETURN sumAmt#AccumFiltered = VAR currentDate = SELECTEDVALUE ( T[Date] ) VAR firstDatValue = MINX ( ALLSELECTED ( T ), T[Date] ) VAR minValue = MINX ( FILTER ( T, T[Date] = firstDatValue ), T[Diff] ) VAR sumAmt = CALCULATE ( SUM ( T[Diff] ), FILTER ( ALLSELECTED ( T ), T[Date] <= currentDate ) ) VAR minOfSum = MIN ( 0, MINX ( FILTER ( ALLSELECTED ( T ), T[Date] <= currentDate ), [Accum] ) ) RETURN IF ( currentDate = firstDatValue && minValue < 0, sumAmt - minValue, sumAmt - minOfSum )If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
- mhrkhader01
Dear ERD,
im writing this in PowerPivot , not in PowerBI. SELECTEDVALUE is not coming with me in DAX. if you have Excel file could you please share it . it will be appreciated. or if you can provide me with another function instead of using SELECTEDVALUE for PowerPivot.
- ERD
You can use MAX instead of SELECTEDVALUE .
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
