Replication of an Excel file calculation - circular dependency issue

[Rolly_oo]

Hi Everyone,

I would like to replicate a calculation (quite simple) in Excel in a PowerBi file. I've tried several times but have an issue of circular dependency. I have not been able to break it so far so i would appreciate any help/ideas on how to model the Excel in PowerBi.

Here below the Excel file:

https://we.tl/t-YYGL9Dn0l5 

Many thanks in advance for your help!

[Rolly_oo]

Hello! Many thanks for the great answer/solution to my issue! 🙂 

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7BCQAhDAXRXv5ZMCbRxVqC/bexSFDj7vUdhjGDZKHMxIIEwkiGFoSdeqDHqXAwcVstPdQi1Ss2qXxiui9Wq/6+jvRbxgs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Inflow = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Inflow", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Capital Calc", each List.Accumulate({0..[Index]},[Roc=0,Cc=160],(state,current)=>[Roc = List.Min({state[Cc],#"Added Index"[Inflow]{current}}),Cc = state[Cc]-Roc]
 )),
    #"Expanded Capital Calc" = Table.ExpandRecordColumn(#"Added Custom", "Capital Calc", {"Cc", "Roc"}, {"Capital calc", "Roc"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Capital Calc",each [Capital calc],each if [Index]>0 then #"Expanded Capital Calc"[Capital calc]{[Index]-1} else [Capital calc],Replacer.ReplaceValue,{"Capital calc"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value", "Realized gain", each if [Capital calc]=0 then [Inflow] else List.Max({0,[Inflow]-[Capital calc]}))
in
    #"Added Custom1"

This is not just a circular dependency but also a reverse circular.  Not something you want to do in Power BI  that often.

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".