Predicting future assets, is this recursion or not?

[IsmoO]

Hi

I've have not found from forums (yet) help for this, whether it's possible or not, as quite a novice still on BI.

I have had tough times trying to convert this simple excel to BI to enable online grahs with different WhatIf parameters, but not sure is it impossible due to recursion limitation; I do need to refer previous row column to get start value for next row. While trying I have bumped occasionally to infamous 'circular depedency detected…' though.

• I've read Greg's post on recursion not possible, though not fully grasping details ;),
• but then again in my another calculation for cumulative returns was able to apply Gerhards way around

So below simple sample shows for each I'm trying to calculate total assets after gains, incomes and expenses, and then use it as beginning value for next year's calculations

same in xls in g-drive

Br Ismo

[IsmoO]

So may I interepret this is impossible task and recursion unavoidable

[Anonymous]

Hi @IsmoO 

I finished reading this blog post and I think the method described by the author is probably the most useful for now.

Previous Value (“Recursion”) in DAX - Microsoft Fabric Community

So in response to your question, I ran some tests:

Here's some dummy data

"Table"

First, go to the power query and add an index column.

Create measures.

Measure 1 = 
IF(
    SELECTEDVALUE('Table'[Index]) <> 1,
    CALCULATE(
        SELECTEDVALUE('Table'[Assets at end of year after gains]),
        FILTER(
            ALL('Table'),
            'Table'[Year] = MAX('Table'[Year]) - 1
        )
    ),
    SELECTEDVALUE('Table'[Assets at beginning of year])
)

Measure 2 = 'Table'[Measure 1] * 0.05

Measure 3 = 
IF(
    ISBLANK(SELECTEDVALUE('Table'[Wages])),
    CALCULATE(
        MAX('Table'[Wages]),
        FILTER(
            ALL('Table'),
            'Table'[Index] = MAX('Table'[Index]) -1)
        ),
    SELECTEDVALUE('Table'[Wages])
)

Measure 4 = 
IF(
    ISBLANK(SELECTEDVALUE('Table'[Costs])),
    CALCULATE(
        MAX('Table'[Costs]),
        FILTER(
            ALL('Table'),
            'Table'[Index] = MAX('Table'[Index]) -1)
        ),
    SELECTEDVALUE('Table'[Costs])
)

Measure 5 = 
'Table'[Measure 1] + 'Table'[Measure 2] + 'Table'[Measure 3] - 'Table'[Measure 4]

Measure 6 = 'Table'[Measure 5] * 0.05 

Measure 7 = 'Table'[Measure 5] + 'Table'[Measure 6] 

Create a new table.

New Table = 
SELECTCOLUMNS(
    'Table',
    "year",
    'Table'[Year],
    "Assets at beginning of year",
    'Table'[Measure 1],
    "Divididends 5%",
    'Table'[Measure 2],
    "Wages",
    'Table'[Measure 3],
    "Costs",
    'Table'[Measure 4],
    "Assets end of year before gains",
    'Table'[Measure 5],
    "Assets value gain 5%",
    'Table'[Measure 6],
    "Assets at end of year after gains",
    'Table'[Measure 7]
    )

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[IsmoO]

Thanks for your help

I'm not sure does this really work, I mean if I remove from excel all but the the start value of the whole chain, it calculates only the first row, see the picture

And the idea really was to dynamically calculate data and alter only start valea (+ all other as well via What-If parameters)