DAX Create a measure which referencing to the previously calculated value of itself (previous period

[BIFarkas]

Hi Experts!

I would like to create a measure in PowerPivot which referencing the preciously calculated value of itself in the previous iteration.

For example: *[Planned Backlog] n = [Planned OIT] period n + (Planned Backlog period n-1 - Planned Revenue period n-1)

So technically it is not referencing to itself but to the previous calculated member along the period dimension.

It is very easy in excel by referencing a previously calculated cell however in a calculated measure it is tricky. I attached the picture to illustrate how I would do it in excel

I want to do this with measure because I want to calculate this dinamically in all the aggregation level. Do you have an idea how can I achieve this? Thanks in advance

[MattAllington]

Presumably you can get the individual row measures to work.  The principle is actually quite simple. You need to be able to relatively reference each row, then filter the data to access the previous row. E.g., if there is a period ID integer, you can access that i reger for the current row with MAX().  Therefore the previous row is MAX()-1. Just use a FILTER inside a CALCULATE to access the previous row. 

[BIFarkas]

Hi Matt!

Thanks for your suggestion and sorry for not coming back earlier. 

Maybe I don't understand your suggestion well but I have a feeling that with your solution I would be able to relatively reference to the previous row from the table context but not the previously calculated value of the same measure. I think for that I would need some kind of recursion. 

I also posted the question to StackOverflow and Tom Martens gave me a hint of Using table-iterators to calculate a future value . He also linked a great article about the issue, but unfortunatelly for my problem I didn't managed to apply yet. 

I am still working on solving it so should you manage to simulate it in a workbook I would appreciate that. 

Have a nice day,

Tom

[v-caliao-msft]

@BIFarkas,

In Power BI data mode, you cannot refer to previous row directly because there is no row order in your data mode. If you want to get previous row, you need to have index column, and then get the previous row value by using LOOKUPVALUE function.

Column = LOOKUPVALUE(Table2[Value],Table2[Index],Table2[Index]-1)

Regards,

Charlie Liao

[Seymouka]

How can you use the beginning balance to trigger a transaction? For example, if beginning balance exceeds $x then $y transaction feeds new $z ending balance. I am trying to create conditional transfers or fees as well as calaculate compounding interest. This can be done in excel with relative ranges but seems DAX prevents since it refers to entire table.