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

amitchandak's avatar
amitchandak
Super User
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 amitchandak. Thanks for your reply.

I got it to work with slight changes. Thanks much for the setup!

 

Btw, how would I summarize these calculations on a year level, and provide year-over-year variance of those sums?