Forum Discussion
Recursive Calculation of Table
Hey,
I have table like this with columns Year, Month, Person, Static (Costs) and Revenue. I am trying to find out, how to provide last column inside Power BI. In column header I wrote Excel Formula with some pseudo code B(x-1)..., which is normaly working inside Excel. Can someone help me please to provide this inside Power BI?
6 Replies
- Greg_Deckler
Community Champion
It's not really clear what you are doing here but if you are trying to do true recursion in DAX, you can't. https://www.linkedin.com/pulse/previous-value-recursion-dax-greg-deckler-microsoft-mvp-/
v-lionel-msftCommunity Support
Hi StepanResl ,
I can't understand your pseudocode, can you give an example? For example, how was the first line calculated?
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey v-lionel-msft ,
it works like:
(SUM value from same column (where were are calculating this), where we are still talking about same Person as in actual calculating row but with (Month - 1)) + (Value from column Revenue (in actual row) - Value from column Static (in actual row)) if result is lower then 0, then result should be zero.- v-lionel-msft
Hi StepanResl ,
1. Add an [Index] column manually.
2. Create measures.
Measure 2 = VAR x = MAX(Sheet5[Revenue]) - MAX(Sheet5[Static]) RETURN IF( x <0, 0, x ) Measure 3 = CALCULATE( [Measure 2], FILTER( ALLSELECTED(Sheet5), Sheet5[Index] = MAX(Sheet5[Index]) - 1 && Sheet5[Person] = MAX(Sheet5[Person]) ) )3. Create a measure again
Calculation = VAR x = MAX(Sheet5[Revenue]) - MAX(Sheet5[Static]) RETURN x + [Measure 3]Although my data is the same as yours, but I started taking values from October 2017, so the returned results are different.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
