Forum Discussion

ankababu007's avatar
ankababu007
Frequent Visitor
4 years ago
Solved

Circular dependency issue

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

eg: I have below dataset

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

Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    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's avatar
    lbendlin
    4 years ago

    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])

     

     

     

7 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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's avatar
      ankababu007
      Frequent Visitor
      4 years ago

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

      • lbendlin's avatar
        lbendlin
        Super User
        4 years ago

        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])

         

         

         

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    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