Circular dependency issue

[ankababu007]

Hi I am trying to find values based on column dependencies.

eg: I have below dataset

YearMonth	B	C	D	E	F	G
2021-Jan	$100	$50	$20	$10	$5	$115
2021-Feb		$55	$30	$20	$10
2021-Mar		$60	$25	$6	$11
2021-Apr		$50	$28	$9	$5
2021-May		$45	$35	$10	$20
2021-Jun		$50	$20	$12	$8
2021-Jul		$80	$20	$10	$5
2021-Aug		$50	$20	$15	$5

G  is calculated by B+C-D-E-F for jan

for 2021-Feb  B should be 115 that we had for column G in 2021-Jan. I do have values for C,D,E,F for all months.

for  2021-Feb G will be 115+55-30-20-10=110

for 2021-Mar B will be 110
The above process has to be repeated.

How to obtain this in power bi desktop.I am new to power bi.

[lbendlin · Accepted solution]

Here is an implementation in Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtT1SsxT0lFSMTQwAFGmYNLIACIEFlGK1YEqdUtNAooogEVNQaQxqmq4Qt/EIphCM4gSsHIzsDpDhDrHArg6qMUWINISzV7fxEqYMhOIvaYI9xkh2etVmodmHsRpRiDSAlldDkydBT4PO5amYzXPFKouFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YearMonth = _t, B = _t, C = _t, D = _t, E = _t, F = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"YearMonth", type date}, {"B", Currency.Type}, {"C", Currency.Type}, {"D", Currency.Type}, {"E", Currency.Type}, {"F", Currency.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "G", each List.Accumulate({0..[Index]},#"Changed Type"{0}[B],(c,s)=> c+#"Added Index"[C]{s}-#"Added Index"[D]{s}-#"Added Index"[E]{s}-#"Added Index"[F]{s})),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom",each [B], each if [Index]=0 then [B] else #"Added Custom"{[Index]-1}[G],Replacer.ReplaceValue,{"B"})
in
    #"Replaced Value"

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 · Accepted solution]

Here it is as a calculated column, but you cannot fill column B.

G = SUMX(FILTER(Table,[YearMonth]<=EARLIER([YearMonth])), [B]+[C]-[D]-[E]-[F])

[ankababu007]

@Ashish_Mathur @lbendlin  Thank you very much both those solutions are working as expected..Thanks a lot.

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-henryk-mstf]

Hi @ankababu007 ,

Agree with the solution given by @lbendlin , it is really a good choice to solve this problem in power query. 

If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Best Regards,
Henry

[lbendlin · Accepted solution]

Here is an implementation in Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtT1SsxT0lFSMTQwAFGmYNLIACIEFlGK1YEqdUtNAooogEVNQaQxqmq4Qt/EIphCM4gSsHIzsDpDhDrHArg6qMUWINISzV7fxEqYMhOIvaYI9xkh2etVmodmHsRpRiDSAlldDkydBT4PO5amYzXPFKouFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YearMonth = _t, B = _t, C = _t, D = _t, E = _t, F = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"YearMonth", type date}, {"B", Currency.Type}, {"C", Currency.Type}, {"D", Currency.Type}, {"E", Currency.Type}, {"F", Currency.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "G", each List.Accumulate({0..[Index]},#"Changed Type"{0}[B],(c,s)=> c+#"Added Index"[C]{s}-#"Added Index"[D]{s}-#"Added Index"[E]{s}-#"Added Index"[F]{s})),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom",each [B], each if [Index]=0 then [B] else #"Added Custom"{[Index]-1}[G],Replacer.ReplaceValue,{"B"})
in
    #"Replaced Value"

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

[ankababu007]

@lbendlin  Thank you very much for your solution. Its working perfectly. Just curious if we can do this using DAX.

[lbendlin · Accepted solution]

Here it is as a calculated column, but you cannot fill column B.

G = SUMX(FILTER(Table,[YearMonth]<=EARLIER([YearMonth])), [B]+[C]-[D]-[E]-[F])