Return date based on a date range

[julesm07]

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 example invoices due Sunday through Saturday in a given week are paid on Friday- so anything in that week, I want to return a pay date of Friday. Or, a customer pays us every Thursday for invoices due the Monday before to the Sunday after. I have 4 such scenarios in my current project. 

Currently, I have 4 tables set up, laid out like below. 

My main table looks like this- the "pay date" is what I want filled, based on the due_date.

I am still fairly new to BI, so I may be missing something obvious, or I may be doing this the complete wrong way. Any help would be appreciated!!

[julesm07]

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

[lbendlin]

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"

[lbendlin]

 I have 4 such scenarios in my current project

Where are they? Don't see them in your post.

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. Screenshots of your source data are not useful.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523