0

I'm struggling to find a way to create a column in Powerbi that is calculated with the previous row of same column. I've seen similar questions in this forum (namely, How to perform sum of previous cells of same column in PowerBI and DAX - formula referencing itself) and I tried to adapt the indicated solutions to my case but without sucess.

Is it possible to create a calculated column with DAX that replicates what i'm doing in excel? or is it one of those cases that is not possible to create a closed-form formula?

excel example

Many thanks, Paul

Paul
  • 1
  • 2
  • Do you really need a column? maybe you can use measure for example: measure1 = CALCULATE(sum(TableRol[val]), FILTER(all(TableRol[Date]), SELECTEDVALUE(TableRol[Date]) >= TableRol[Date] )) – msta42a May 28 '21 at 10:24
  • I can also use a measure. no problem with that. the problem is that I need to calculate the max(Table[value]+Table[output],0) of the previous row as descrived in example. with your sugestion i'll get the accumulated value. Any other sugestion? Thank you! – Paul May 28 '21 at 11:38
  • i try every trick that i know, but without success. https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/ – msta42a May 31 '21 at 10:41
  • Same here. it looks like that this kind of calculation simply isn't possible. thank you for your effort – Paul Jun 01 '21 at 18:37

1 Answers1

0

The example from @msta42a worked to accumulate all the values. I modified to add a TOPN filter, that will sum only the last 7 rows.

measure1 =
CALCULATE(
SUM(
    Recovery[recovery_calculated]),
    TOPN(
        7,
        FILTER(
            all(Recovery[Date]), 
            SELECTEDVALUE(Recovery[Date]) >= Recovery[Date] 
        ),
        Recovery[Date],
        DESC
    )

)

akuna
  • 1
  • 1