Forum Discussion

Satya2804's avatar
Satya2804
Frequent Visitor
3 years ago

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 Suppl...
Tips and Tricks
Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

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

  • Satya2804's avatar
    Satya2804
    Frequent Visitor
    3 years ago

    Hi Ashish

    Yeah I can add a quarter end date as below:

    Quarter end dateQuarterSupplyOutlook
    31-03-2023Q13125
    30-06-2023Q23841
    30-09-2023Q34222
    31-12-2023Q44522
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      3 years ago

      Hi,

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

      • lbendlin's avatar
        lbendlin
        Super User
        3 years ago

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