Create Custom Column by using data from previous row

[gv]

I am currently creating a CustomColumn in a existing model where I need to use data from previous rows to calucate the column value. This is already existing in Excel which I am currently migrating it to PowerBI.

In the screenshot shared below, "FinalResult" is a calculated column which uses a formula to calculate the result.

D5= IF(SUM(C3:C5)>=2,B5,"")

Since, it uses 2 previous rows to determine the value, the first 2 cells are empty and the formula starts only from D4 till end. Kindly let me know how do I create a similar column in PowerBI. 

Note: I already have the other 3 columns in model and only need to create "FinalResult"

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula seems to work

=if(CALCULATE(SUM(Data[Dependency]),FILTER(Data,Data[ID]>=EARLIER(Data[ID])-2&&Data[ID]<=EARLIER(Data[ID])))>=2,Data[TestResult],BLANK())

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula seems to work

=if(CALCULATE(SUM(Data[Dependency]),FILTER(Data,Data[ID]>=EARLIER(Data[ID])-2&&Data[ID]<=EARLIER(Data[ID])))>=2,Data[TestResult],BLANK())

Hope this helps.

[gv]

I am creating this model in PowerBI Desktop and can you help to write the same query accepted by PowerBI Desktop. I have trouble executing the query

[Ashish_Mathur · Accepted solution]

Hi,

You have to go to Modelling > Add column and paste the query there.