Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## How to avoid circular dependency using previous row

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
8 REPLIES 8
Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Frequent Visitor

Hi Ashish

Yeah I can add a quarter end date as below:

 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
Super User

Hi,

That is definitely cicular logic.  I misread your question initially.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User

Well, it may be circular but List.Accumulate can handle it.

``````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)=>[
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".

Super User

Hi,

Thank you for sharing this.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User

@Ashish_Mathur I learned how to better carry multiple variables through the iterations, and how (and when) to address them in in each cycle ( like the " state[Long] " example). I am more and more impressed by the versatility of List.Accumulate.

Super User

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

Frequent Visitor

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

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors