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