Replication of an Excel file calculation - circular dependency issue

Rolly_oo — Tue, 11 Jul 2023 14:03:03 GMT

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!

Re: Replication of an Excel file calculation - circular dependency issue

lbendlin — Sun, 16 Jul 2023 22:04:40 GMT

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".

Re: Replication of an Excel file calculation - circular dependency issue

Rolly_oo — Mon, 24 Jul 2023 14:05:32 GMT

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

topic Replication of an Excel file calculation - circular dependency issue in DAX Commands and Tips

Replication of an Excel file calculation - circular dependency issue

Re: Replication of an Excel file calculation - circular dependency issue

Re: Replication of an Excel file calculation - circular dependency issue