How to avoid circular dependency using previous row

Question

Below is a sample data - first 3 columns are my inputs. Total Supply, Match and Long are my calculated columns.

Total Supply = Current Quarter Supply + Previous quarter Long. Example Total Supply Q2 = 38 + 6 = 44

Match = Min(Outlook, Total Supply). Example Match Q2 = Min (41, 44) = 41

Long = Total Supply - Match. Example Long Q2 = 44-41 = 3

PowerBI gives a circular dependency error but Total Supply is actually using previous quarter Long and not current quarter long, so logically it is not circular.

Can anyone help replicate the measures in PowerBI?

Quarter	Supply	Outlook	Total Supply	Match	Long
Q1	31	25	31	25	6
Q2	38	41	44	41	3
Q3	42	22	45	22	23
Q4	45	22	68	22	46

lbendlin · Answer

Well, it may be circular but List.Accumulate can handle it.
&nbsp;
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIGEUamSrE60UpGIL4FkDAxBPONQUyQoJERmG8C4ptC+bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quarter = _t, Supply = _t, Outlook = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Quarter", Int64.Type}, {"Supply", Int64.Type}, {"Outlook", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Accumulate({1..[Quarter]},[Total=0, Match=0, Long=0],(state,current)=&gt;[ 
    Total = #"Changed Type"{current-1}[Supply] + state[Long],
    Match = List.Min({Total,#"Changed Type"{current-1}[Outlook]}),
    Long = Total-Match ]
)),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Total", "Match", "Long"}, {"Total", "Match", "Long"})
in
    #"Expanded Custom"
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".

lbendlin · Answer

Do you need this in Power BI? As a measure or calculated column?&nbsp; Or can it be done in Power Query?

ashish_mathur · Answer

Hi,
Do you have a proper Date column?&nbsp; If yes, then share the download link of the PBI file.

satya2804 · Answer

Was trying to solve for it in a measure, but even if it is a PowerQuery it works..

satya2804 · Answer

Hi AshishYeah I can add a quarter end date as below:Quarter end dateQuarterSupplyOutlook31-03-2023Q1312530-06-2023Q2384130-09-2023Q3422231-12-2023Q44522

Forum Discussion

How to avoid circular dependency using previous row

8 Replies

Related content

Circular dependency

Circular Dependency

DAX Help - Circular Dependency

Circular Dependency Error

A Circular Dependency was detected

Quarter end date	Quarter	Supply	Outlook
31-03-2023	Q1	31	25
30-06-2023	Q2	38	41
30-09-2023	Q3	42	22
31-12-2023	Q4	45	22