Forum Discussion
Return date based on a date range
Hi, Wasn't sure how to title this one... This question revolves around AR/AP payments. I have a due date for an invoice. Based on that due date, I want to return a pay on date. So, for exampl...
I've attached two tables, which represents what I am trying to accomplish.
My goal is to take the due date (ex table 1, line 1 9/4/22) and find the date range it fits into (second table, start date/end date) and return the corresponding pay date (in this case it would be 9/9/22). Hopefully this makes sense- if not let me know and I can try and explain it better.
| Terms | Vendor | id-apdoc | Document_Date | Original_Amt | Open_Amt | Open_Amt_Curr_Per | Due_Date | Discount_Amt | Discount_Date | id-ap |
| Net 40 | 365 | 579701 | 7/26/2022 0:00 | $3,597.52 | $3,597.52 | $3,597.52 | 9/4/2022 0:00 | $0 | 7/26/2022 0:00 | |
| Net 40 | 365 | 579700 | 7/26/2022 0:00 | $3,582.08 | $3,582.08 | $3,582.08 | 9/4/2022 0:00 | $0 | 7/26/2022 0:00 | |
| Net 40 | 365 | 580176 | 8/1/2022 0:00 | $84.09 | $84.09 | $84.09 | 9/10/2022 0:00 | $0 | 8/1/2022 0:00 | |
| Net 40 | 365 | 580175 | 8/1/2022 0:00 | $2,327.25 | $2,327.25 | $2,327.25 | 9/10/2022 0:00 | $0 | 8/1/2022 0:00 | |
| Net 40 | 365 | 580067 | 7/29/2022 0:00 | $498.44 | $498.44 | $498.44 | 9/7/2022 0:00 | $0 | 7/29/2022 0:00 | |
| Net 40 | 365 | 580068 | 7/29/2022 0:00 | $1,795.69 | $1,795.69 | $1,795.69 | 9/7/2022 0:00 | $0 | 7/29/2022 0:00 | |
| Net 40 | 365 | 579329 | 7/21/2022 0:00 | $29.70 | $29.70 | $29.70 | 8/30/2022 0:00 | $0 | 7/21/2022 0:00 | |
| Net 40 | 365 | 579330 | 7/21/2022 0:00 | $417.81 | $417.81 | $417.81 | 8/30/2022 0:00 | $0 | 7/21/2022 0:00 | |
| Net 40 | 365 | 579333 | 7/21/2022 0:00 | $999 | $999 | $999 | 8/30/2022 0:00 | $0 | 7/21/2022 0:00 | |
| Net 40 | 365 | 579078 | 7/19/2022 0:00 | $1,260 | $1,260 | $1,260 | 8/28/2022 0:00 | $0 | 7/19/2022 0:00 | |
| Net 40 | 365 | 579332 | 7/21/2022 0:00 | $450 | $450 | $450 | 8/30/2022 0:00 | $0 | 7/21/2022 0:00 | |
| Net 40 | 365 | 579331 | 7/21/2022 0:00 | $730.73 | $730.73 | $730.73 | 8/30/2022 0:00 | $0 | 7/21/2022 0:00 | |
| Net 40 | 365 | 579081 | 7/19/2022 0:00 | $610.08 | $610.08 | $610.08 | 8/28/2022 0:00 | $0 | 7/19/2022 0:00 |
| start-date | end-date | pay-date | id-ap |
| 8/14/2022 | 8/20/2022 | 8/19/2022 | 1 |
| 8/21/2022 | 8/27/2022 | 8/26/2022 | 2 |
| 8/28/2022 | 9/3/2022 | 9/2/2022 | 3 |
| 9/4/2022 | 9/10/2022 | 9/9/2022 | 4 |
| 9/11/2022 | 9/17/2022 | 9/16/2022 | 5 |
| 9/18/2022 | 9/24/2022 | 9/23/2022 | 6 |
| 9/25/2022 | 10/1/2022 | 9/30/2022 | 7 |
| 10/2/2022 | 10/8/2022 | 10/7/2022 | 8 |
| 10/9/2022 | 10/15/2022 | 10/14/2022 | 9 |
| 10/16/2022 | 10/22/2022 | 10/21/2022 | 10 |
| 10/23/2022 | 10/29/2022 | 10/28/2022 | 11 |
| 10/30/2022 | 11/5/2022 | 11/4/2022 | 12 |
| 11/6/2022 | 11/12/2022 | 11/11/2022 | 13 |
| 11/13/2022 | 11/19/2022 | 11/18/2022 | 14 |
Table PayPeriods:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZHLDcUgDAR7yTkSrM23lij9txHIi+19twFG7GKu6xgJJUkWOc7FkoMxjXHc5zYFZHbiZixmDtuZSQPFUF9vphJnyMGeXD4PILETe3I1k5KFrhev0T5Tqr8vJ7pfvUh/zV2MzEEcMzBz8p1/AdHF3Ci/IzgjBo3sHZQFzolhA2bHI9boKrHXwO+v1lajYwgvooaaDGVh8iJqrG+7Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"start-date" = _t, #"end-date" = _t, #"pay-date" = _t, #"id-ap" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"start-date", type datetime}, {"end-date", type datetime}, {"pay-date", type datetime}, {"id-ap", Int64.Type}})
in
#"Changed Type"Table Transactions:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZTNCcMwDEZXKaFHI+vHtqQO0QVKj92g+9OU9JJaDiG52A8s6cEn8OMx3V/vS8EpTdLqfFZ1RZpBM7fMyHzBG37fr5KqK1TeYM9l3YP9pGeKpUHpMtwY0Db4qNSQtM1gmdb9VgA9BM+EvWs9YKSqkYqTsALXDT7uxKZLEr7uL25QyoA8a5im79BZqKOkXqH5Bh+Vqgv7UvofrIPGYFmCPP8mDGQSlH7DIwWjAZ3ySehz9+46oUFdFkfd4rjFYJktktGOhQnHGdb+OhUdhRoVBJUBncnQKMywEf5+rID2xfj8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Terms = _t, Vendor = _t, #"id-apdoc" = _t, Document_Date = _t, Original_Amt = _t, Open_Amt = _t, Open_Amt_Curr_Per = _t, Due_Date = _t, Discount_Amt = _t, Discount_Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Document_Date", type datetime}, {"Original_Amt", Currency.Type}, {"Open_Amt", Currency.Type}, {"Open_Amt_Curr_Per", Currency.Type}, {"Due_Date", type datetime}, {"Discount_Amt", Currency.Type}, {"Discount_Date", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (k)=>Table.SelectRows(PayPeriods,each [#"start-date"]<=k[Due_Date] and [#"end-date"]>=k[Due_Date])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"pay-date", "id-ap"}, {"pay-date", "id-ap"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"pay-date", type datetime}})
in
#"Changed Type1"