cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
julesm07
Regular Visitor

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 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. 

julesm07_0-1660672377974.png

 

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

julesm07_1-1660672463234.png

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!!

 

 

 

 

3 REPLIES 3
julesm07
Regular Visitor

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. 

TermsVendorid-apdocDocument_DateOriginal_AmtOpen_AmtOpen_Amt_Curr_PerDue_DateDiscount_AmtDiscount_Dateid-ap
Net 403655797017/26/2022 0:00$3,597.52$3,597.52$3,597.529/4/2022 0:00$07/26/2022 0:00 
Net 403655797007/26/2022 0:00$3,582.08$3,582.08$3,582.089/4/2022 0:00$07/26/2022 0:00 
Net 403655801768/1/2022 0:00$84.09$84.09$84.099/10/2022 0:00$08/1/2022 0:00 
Net 403655801758/1/2022 0:00$2,327.25$2,327.25$2,327.259/10/2022 0:00$08/1/2022 0:00 
Net 403655800677/29/2022 0:00$498.44$498.44$498.449/7/2022 0:00$07/29/2022 0:00 
Net 403655800687/29/2022 0:00$1,795.69$1,795.69$1,795.699/7/2022 0:00$07/29/2022 0:00 
Net 403655793297/21/2022 0:00$29.70$29.70$29.708/30/2022 0:00$07/21/2022 0:00 
Net 403655793307/21/2022 0:00$417.81$417.81$417.818/30/2022 0:00$07/21/2022 0:00 
Net 403655793337/21/2022 0:00$999$999$9998/30/2022 0:00$07/21/2022 0:00 
Net 403655790787/19/2022 0:00$1,260$1,260$1,2608/28/2022 0:00$07/19/2022 0:00 
Net 403655793327/21/2022 0:00$450$450$4508/30/2022 0:00$07/21/2022 0:00 
Net 403655793317/21/2022 0:00$730.73$730.73$730.738/30/2022 0:00$07/21/2022 0:00 
Net 403655790817/19/2022 0:00$610.08$610.08$610.088/28/2022 0:00$07/19/2022 0:00 
start-dateend-datepay-dateid-ap
8/14/20228/20/20228/19/20221
8/21/20228/27/20228/26/20222
8/28/20229/3/20229/2/20223
9/4/20229/10/20229/9/20224
9/11/20229/17/20229/16/20225
9/18/20229/24/20229/23/20226
9/25/202210/1/20229/30/20227
10/2/202210/8/202210/7/20228
10/9/202210/15/202210/14/20229
10/16/202210/22/202210/21/202210
10/23/202210/29/202210/28/202211
10/30/202211/5/202211/4/202212
11/6/202211/12/202211/11/202213
11/13/202211/19/202211/18/202214

 

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
Super User
Super User

 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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.