Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
afat_aliyev
Regular Visitor

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:

 

InvoiceNumberPostDatePreviousPostDate
000110/30/2020 
000211/27/202010/30/2020
000312/25/202011/27/2020

 

Is there a way in DAX or in PowerQuery to add this column? Thank you for your time.

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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"



 

View solution in original post

4 REPLIES 4
Saap
Resolver III
Resolver III

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.

v-jayw-msft
Community Support
Community Support

Hi @afat_aliyev ,

 

Please check the DAX formula:

Column =
CALCULATE (
    MAX ( 'Table'[PostDate] ),
    FILTER (
        'Table',
        'Table'[InvoiceNumber]
            = EARLIER ( 'Table'[InvoiceNumber] ) - 1
    )
)

vjaywmsft_0-1655799536961.png

 

Best Regards,

Jay

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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"



 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.