Forum Discussion

JusticeBaird's avatar
JusticeBaird
Frequent Visitor
4 years ago
Solved

DAX - Need next row value

Hello -    I am attempting to create a new column utilizing DAX (or taking advice on how to accomplish the following).  I have a dataset that sometimes includes 'Beginning Balance' for [Date] and N...
DAX
Help appreciated
Help me please
Help Requested
  • smpa01's avatar
    smpa01
    4 years ago

    JusticeBaird  this is not an analysis level task, rather it is a data level task which can be resolved by using PQ in the following way

     

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckpNV3BKzFHSUYIgQwMDA6VYnWglIwMjQ10DIDIBijol5mUDKV2gLJCyhCpB12uKotcQpB0o6qgfANJqCtZqAqJiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Account = _t, Amount = _t, Balance = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Account", type text}, {"Amount", Int64.Type}, {"Balance", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "newAccount", each let x = #"Added Index"[Account],
    y = #"Added Index"[Date],
    z = if [Date]="Beg Bal" then x{[Index]+1} else [Account]

    in z),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

     

     

     

     

     

JusticeBaird's avatar
JusticeBaird
Frequent Visitor
4 years ago

Sample data is stated below BeaBF . Please note that my only goal is to bring in the applicable account name for for the blank 'Beginning Balance' rows.  Please let me know if you have any further questions. 

 

Date         | Account     | Amount     | Balance 

Beg Bal.   |                    |                   | 1000

1/4/2021 | Bank           |  -100          | 900

 

Beg Bal.  |                     |                   | 5000

1/11/21  | A/P              |     -500       | 4500   

  • BeaBF's avatar
    BeaBF
    Icon for Super User rankSuper User
    4 years ago

    JusticeBaird 

    yes, but if the lines were not in this order, you need a rule to assign the occunt bank rather than loan. what is this rule?

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    JusticeBaird  this is not an analysis level task, rather it is a data level task which can be resolved by using PQ in the following way

     

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckpNV3BKzFHSUYIgQwMDA6VYnWglIwMjQ10DIDIBijol5mUDKV2gLJCyhCpB12uKotcQpB0o6qgfANJqCtZqAqJiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Account = _t, Amount = _t, Balance = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Account", type text}, {"Amount", Int64.Type}, {"Balance", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "newAccount", each let x = #"Added Index"[Account],
    y = #"Added Index"[Date],
    z = if [Date]="Beg Bal" then x{[Index]+1} else [Account]

    in z),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

     

     

     

     

     

    • JusticeBaird's avatar
      JusticeBaird
      Frequent Visitor
      4 years ago

      I copy/pasted you logic and came up with the same results as you, now which part of the logic to I need to update to return the full table?

       

      Thank you,

       

      • smpa01's avatar
        smpa01
        Icon for Community Champion rankCommunity Champion
        4 years ago

        JusticeBaird try running the code from #"Added Index" onwards in your original dataset. It should not create any issue.