Forum Discussion

IsmoO's avatar
IsmoO
Frequent Visitor
2 years ago

Predicting future assets, is this recursion or not?

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

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      IsmoO
      Frequent Visitor
      2 years ago

      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)

  • IsmoO's avatar
    IsmoO
    Frequent Visitor
    2 years ago

    So may I interepret this is impossible task and recursion unavoidable