DAX Measure use previous row

[nathanprats]

Hi all, 

I have a DAX measure I'm stuck with for a few days now. 

The idea is to get the "Result" as a DAX measure in PBI. 

Here's the equivalent in Excel : 

• Result = [@[Previous Result]]*[@Rate]+[@X]

I encouter circular references with PBI, as the Previous Resultreferences the result, and result references the Previous Result.

In my screenshot, there are 4 rows, but I could have much more. 

Any idea how to solve this ? 

[v-kelly-msft]

Hi @nathanprats ,

What you need to do is to create 3 calculated columns:

1 An index column using query editor, “add column”->”Index column”->”from 1”,then you will see as below:

2.Create a column which calculates the previous result based on the value of “Result”:

Select “calculated column”:

dax PreviousResult = 
var a ='Table'[Result ]
var b ='Table'[Index]
Return
CALCULATE(SUM('Table'[Result ]),FILTER('Table',[index]=b-1))

3. Create a column which calculates result based the value of previous result:

dax Result = 'Table'[dax PreviousResult]*'Table'[Rate]+'Table'[X]

Then you will find see as below:

For the related .pbix file ,you can turn to URL:https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-kellya_microsoft_com/EUhsDoRxQkREss9Xo4Hawyo...

Hope this would help.

Best Regards,

Kelly

[nathanprats]

Hi, 

This doesn't work, as you're using the result itself in the "dax PreviousResult" column. 

[v-kelly-msft · Accepted solution]

Hi @nathanprats ，

So you wanna calculate the value  of “result” by column “Number ”,”x”  and “Rate”,right?

I have modified the measure,pls see below:

Measure = MAX('Table'[Previous Result])*MAX('Table'[Rate])+MAX('Table'[X])
 

You can also create a calculated column,which is as below:

Column = 'Table'[Previous Result]*'Table'[Rate]+'Table'[X]

With both ways above,you will finally see as below:

For the related .pbix file,pls click here.

Hope this would help.

Best Regards,

Kelly

[TomMartens]

Hey @nathanprats , 

I'm wondering if you can adapt this https://www.minceddata.info/2018/02/21/using-table-iterators-to-calculate-a-future-value/ to your needs.

Regards,

Tom