cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Sunflower
Frequent Visitor

Date before the latest

Hello smart guys,

I am trying to solve this in Power Query, because there are some other transformations related. Hope you can help.

 

Situation: Customers pay their order in instalments and each installments has Due Date (the date by when it should be paid) and Collected Date (the date when it's paid). If there is no CollectedDate, it means that instalment was not paid.

 

Goal: I want to get a table grouped by Orders which will give me:

  • MinDueDate = Earliest Due Date (first instalment)
  • LastPayDate = Latest Due Date if it is collected (latest paid instalment), ignore not paid instalments
  • BeforeLastPayDate = Second Latest Due Date (instalment before the last paid)

 

Screenshot_1.png

 

Data can be found here

 

Thanks for looking at it.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Date before the latest

Hi @Sunflower ,

 

Please check the following steps as below.

 1. Filter the table as below based on COLLECTED  DATE column in power query.

Capture.PNG

2. Group the table by Order.

 

group.PNG

 

3. Insert a custom column as below.

cus.PNG

 

4. After that, Merge the table based on Order and index column from both tables as the screen shot following.

merge.PNG

5. Expand the due date column as we need and deleted the uncessary columns to get the excepted reuslt.

fin.PNG

 

For more details, please check the M code as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdNLDoJAEATQu8waw1TPj7mAiRsvQLj/NUyUtKVWx10TXoCpavY9XW/3C3LGyGlJwFpWy3l+z+lYfmkl6rNJ2oj6XCTtRH2ukg6iPjdJN6I+d0knUZ8HUzPY6z4yYboINFhDRkbaWJtM7dSnKTKu6IlVJkaaVwFNhkaatwH9X268EPDutkDzTsDrm3ES7w5ziIy6+0QN4r2GoF3XvI5mQbuueSOtBO0+9Wlq0C6bFnTKpusmaxnqy4Y+9Vvz72KbPrVrzny1Sac+Hg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Order = _t, #"COLLECTED DATE" = _t, #"DUE DATE" = _t, instalment = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", type text}, {"COLLECTED DATE", type date}, {"DUE DATE", type date}, {"instalment", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([COLLECTED DATE] <> null)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Order"}, {{"max due date", each List.Max([DUE DATE]), type date}, {"min collect date", each List.Min([COLLECTED DATE]), type date}, {"instalment", each List.Max([instalment]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [instalment]-1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Order", "Custom"}, Table, {"Order", "instalment"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"DUE DATE"}, {"Table.DUE DATE"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"instalment", "Custom"})
in
    #"Removed Columns"

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

4 REPLIES 4
Community Support
Community Support

Re: Date before the latest

Hi @Sunflower ,

 

Please create a calculated table as below.

Table 2 = 
VAR a =
    SUMMARIZECOLUMNS (
        'Table'[Order],
        FILTER ( 'Table', 'Table'[COLLECTED DATE] <> BLANK () ),
        "maxdate", MAX ( 'Table'[DUE DATE] ),
        "min", MIN ( 'Table'[COLLECTED DATE] )
    )
RETURN
    ADDCOLUMNS (
        a,
        "last", CALCULATE (
            MAX ( 'Table'[DUE DATE] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Order] ),
                'Table'[COLLECTED DATE] <> BLANK ()
                    && 'Table'[DUE DATE] <> [maxdate]
            )
        )
    )

Capture.PNG

 

For more details, please check the pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Sunflower
Frequent Visitor

Re: Date before the latest

Thank you, 

 

Community Support
Community Support

Re: Date before the latest

Hi @Sunflower ,

 

Please check the following steps as below.

 1. Filter the table as below based on COLLECTED  DATE column in power query.

Capture.PNG

2. Group the table by Order.

 

group.PNG

 

3. Insert a custom column as below.

cus.PNG

 

4. After that, Merge the table based on Order and index column from both tables as the screen shot following.

merge.PNG

5. Expand the due date column as we need and deleted the uncessary columns to get the excepted reuslt.

fin.PNG

 

For more details, please check the M code as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdNLDoJAEATQu8waw1TPj7mAiRsvQLj/NUyUtKVWx10TXoCpavY9XW/3C3LGyGlJwFpWy3l+z+lYfmkl6rNJ2oj6XCTtRH2ukg6iPjdJN6I+d0knUZ8HUzPY6z4yYboINFhDRkbaWJtM7dSnKTKu6IlVJkaaVwFNhkaatwH9X268EPDutkDzTsDrm3ES7w5ziIy6+0QN4r2GoF3XvI5mQbuueSOtBO0+9Wlq0C6bFnTKpusmaxnqy4Y+9Vvz72KbPrVrzny1Sac+Hg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Order = _t, #"COLLECTED DATE" = _t, #"DUE DATE" = _t, instalment = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", type text}, {"COLLECTED DATE", type date}, {"DUE DATE", type date}, {"instalment", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([COLLECTED DATE] <> null)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Order"}, {{"max due date", each List.Max([DUE DATE]), type date}, {"min collect date", each List.Min([COLLECTED DATE]), type date}, {"instalment", each List.Max([instalment]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [instalment]-1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Order", "Custom"}, Table, {"Order", "instalment"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"DUE DATE"}, {"Table.DUE DATE"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"instalment", "Custom"})
in
    #"Removed Columns"

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Sunflower
Frequent Visitor

Re: Date before the latest

Yeeees! this helps.

 

thank you very much

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors