Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Incremental/Accumulative DAX/Query Calculation

Hi Power BI gurus! I'm struggling with getting the incremental or accumulative formula over the years. Namely, my data has Area column, Date (Year) column, Value and Value to be Added. What I need i...
Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , A new column

     

    new column =
    var _min = minx(filter(Table, [Area] = earlier([Area]) ), Table[Year])
    var _minval = sumx(filter(Table, [Area] = earlier([Area]) && Table[Year] =_min ), Table[Value])
    return
    sumx(filter(Table, [Area] = earlier([Area]) && Table[Year] >=_min && Table[Year] <= earlier(Table[Year]) ), Table[Value]) + _minval

     

    A new measure


    new measure =
    var _min = minx(filter(allselected(Table), [Area] = max([Area]) ), Table[Year])
    var _minval = sumx(filter(allselected(Table), [Area] = max([Area]) && Table[Year] =_min ), Table[Value])
    return
    sumx(filter(allselected(Table), [Area] = max([Area]) && Table[Year] >=_min && Table[Year] <= max(Table[Year]) ), Table[Value]) + _minval

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

Please refer to my pbix to see if it helps you.

Create measures.

 

Measure = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Year(Date)]<=SELECTEDVALUE('Table'[Year(Date)])&&'Table'[Area]=SELECTEDVALUE('Table'[Area])))

difference_ =
VAR next_value =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Area] = SELECTEDVALUE ( 'Table'[Area] )
                && 'Table'[Year(Date)]
                    = SELECTEDVALUE ( 'Table'[Year(Date)] ) + 1
        )
    )
VAR this_value =
    MAX ( 'Table'[Value] )
VAR _a =
    CALCULATE (
        next_value - this_value,
        FILTER ( ALL ( 'Table' ), 'Table'[Area] = SELECTEDVALUE ( 'Table'[Area] ) )
    )
RETURN
    IF ( next_value = BLANK (), BLANK (), _a )

 

If I have misunderstood your meaning, please provide more details about your desired output.

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.