I Need a Measure not a Power Query Formula - Accumulative column

[mhrkhader01]

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

[ERD · Accepted solution]

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.

[ERD · Accepted solution]

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 · Accepted solution]

@mhrkhader01 ,

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.

[mhrkhader01]

Dear ERD, i have a Question.

suppose i have in PowerPivot 20 Measures, what i want is that whenever i drag the measure , any of the 20 Measures to Values Section in Pivot Table, i need that Measure to be divided by 12. how to do that. so the final result in Pivot Table for that Measure is tthe result of dividing the measure by 12. is that Possible? without changing all 20 Measures formulas.

[ERD]

I believe this can be achieved by using Calculation groups.

Here is what you can read on this: https://dax.guide/selectedmeasure/ (article with references).

[mhrkhader01]

Dear ERD,

Thanks. But in PowerPivot, the function SELECTEDMEASURE is not available. do you have another solution? 

[amitchandak]

@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"))

[mhrkhader01]

Dear 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.