Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |