Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 |
Hi,
Do you have a proper Date column? If yes, then share the download link of the PBI file.
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 |
Hi,
That is definitely cicular logic. I misread your question initially.
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".
Hi,
Thank you for sharing this.
@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.
Do you need this in Power BI? As a measure or calculated column? Or can it be done in Power Query?
Was trying to solve for it in a measure, but even if it is a PowerQuery it works..
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
96 | |
90 | |
79 | |
67 |
User | Count |
---|---|
153 | |
125 | |
114 | |
111 | |
95 |