Forum Discussion
Finding the Previous Invoice's Post Date
Hello, everyone!
I kindly need your help in the following case: I have a table of invoices with InvoiceNumber and PostDate columns. I would like to add another column to this table for the previous invoice's PostDate. Please see the example:
| InvoiceNumber | PostDate | PreviousPostDate |
| 0001 | 10/30/2020 | |
| 0002 | 11/27/2020 | 10/30/2020 |
| 0003 | 12/25/2020 | 11/27/2020 |
Is there a way in DAX or in PowerQuery to add this column? Thank you for your time.
Hi afat_aliyev - I would use Power Query in this scenario. If you add add 2 Index columns to the table. The first starts a 1 and the second starts at 0. The table can be joined to itself on the Index columns. Use expand columns to get the additional column with the previous date. The following script shows an example of this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTSUTI00Dc20DcyMDJQitUBixqBRA31jcxRRI1Bokb6RqZQ0VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [InvoiceNumber = _t, PostDate = _t]), #"Parsed Date" = Table.TransformColumns(Source,{{"PostDate", each Date.FromText(_, "en-US"), type date}}), #"Added Index" = Table.AddIndexColumn(#"Parsed Date", "Index - 1", 1, 1, Int64.Type), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index", 0, 1, Int64.Type), #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index - 1"}, "Added Index1", JoinKind.LeftOuter), #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"PostDate"}, {"Previous Post Date"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index1",{"Index - 1", "Index"}), #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"InvoiceNumber", Order.Ascending}}) in #"Sorted Rows"
4 Replies
Hi afat_aliyev - I would use Power Query in this scenario. If you add add 2 Index columns to the table. The first starts a 1 and the second starts at 0. The table can be joined to itself on the Index columns. Use expand columns to get the additional column with the previous date. The following script shows an example of this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTSUTI00Dc20DcyMDJQitUBixqBRA31jcxRRI1Bokb6RqZQ0VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [InvoiceNumber = _t, PostDate = _t]), #"Parsed Date" = Table.TransformColumns(Source,{{"PostDate", each Date.FromText(_, "en-US"), type date}}), #"Added Index" = Table.AddIndexColumn(#"Parsed Date", "Index - 1", 1, 1, Int64.Type), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index", 0, 1, Int64.Type), #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index - 1"}, "Added Index1", JoinKind.LeftOuter), #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"PostDate"}, {"Previous Post Date"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index1",{"Index - 1", "Index"}), #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"InvoiceNumber", Order.Ascending}}) in #"Sorted Rows"Hi afat_aliyev
Try to do this in your report: https://www.youtube.com/watch?v=xN2IRXQ2CvI
It should work fine and be less performance heavy than doing calculated column in DAX.Saap - I like this video, but Matt missed a trick. He should have joined on Customer and Index Columns to avoid the need for the if Customer = Customer.
Hi afat_aliyev ,
Please check the DAX formula:
Column = CALCULATE ( MAX ( 'Table'[PostDate] ), FILTER ( 'Table', 'Table'[InvoiceNumber] = EARLIER ( 'Table'[InvoiceNumber] ) - 1 ) )Best Regards,
Jay
