topic Re: DAX - Need next row value in DAX Commands and Tips

DAX - Need next row value

JusticeBaird — Thu, 04 Nov 2021 03:03:15 GMT

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 Null for [Account]. I am trying to replace the Null with the next rows value (within the same column). Please reference the attached picture for current data set on the left and my future goal on the right.

Thank you,

Justice

Re: DAX - Need next row value

BeaBF — Thu, 04 Nov 2021 08:58:39 GMT

JusticeBaird Could you paste the data on which to calculate the column?

Thx,

Re: DAX - Need next row value

JusticeBaird — Thu, 04 Nov 2021 14:24:50 GMT

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

Re: DAX - Need next row value

BeaBF — Thu, 04 Nov 2021 14:30:39 GMT

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?

Re: DAX - Need next row value

smpa01 — Thu, 04 Nov 2021 15:11:22 GMT

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"

Re: DAX - Need next row value

JusticeBaird — Fri, 05 Nov 2021 00:38:53 GMT

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,

Re: DAX - Need next row value

smpa01 — Fri, 05 Nov 2021 00:41:29 GMT

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

Re: DAX - Need next row value

JusticeBaird — Fri, 05 Nov 2021 01:07:50 GMT

I am still unable to get it to return the information I need.. Would it help if I sent the current Source Logic and see what parts I should update to incorporate your logic?

let
Source = Excel.Workbook(File.Contents("C:\Users\justi\Desktop\TG\Power BI\Quickbooks\General Ledger.xlsx"), null, false),
#"General Ledger_sheet" = Source{[Item="General Ledger",Kind="Sheet"]}[Data],
FilterNullAndWhitespace = each List.Select(_, each _ <> null and (not (_ is text) or Text.Trim(_) <> "")),
#"Removed Bottom Rows" = Table.RemoveLastN(#"General Ledger_sheet", each try List.IsEmpty(List.Skip(FilterNullAndWhitespace(Record.FieldValues(_)), 1)) otherwise false),
#"Removed Top Rows" = Table.Skip(#"Removed Bottom Rows", each try List.IsEmpty(List.Skip(FilterNullAndWhitespace(Record.FieldValues(_)), 1)) otherwise false),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Date", type text}, {"Account", type text}, {"Transaction Type", type text}, {"Num", type any}, {"Memo/Description", type text}, {"Split", type text}, {"Amount", type number}, {"Balance", type number}, {"Name", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Weird Name"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type),
#"Sorted Rows" = Table.Sort(#"Added Index",{{"Index", Order.Ascending}})
in
#"Sorted Rows"