Forum Discussion
Add calculated fields based on date range
I need help!
I have two tables, I want to add four colums to Table 1, based on the Completed Date, where the Completed Date falls between the Start Date and End Date from Table 2.
Table 1 - Before
| Work ID | Completed Date |
| 112 | 6/29/2021 |
| 113 | 7/11/2021 |
| 114 | 7/13/2021 |
| 115 | 7/20/2021 |
Table 2
| Accounting Period | Fiscal Year | Calendar Year | Payperiod ID | Start Date | End Date |
| AP01 | FY22 | 21 | 14 | 6/28/2021 | 7/11/2021 |
| AP01 | FY22 | 21 | 15 | 7/12/2021 | 7/25/2021 |
| AP02 | FY22 | 21 | 16 | 7/26/2021 | 8/8/2021 |
| AP02 | FY22 | 21 | 17 | 8/9/2021 | 8/22/2021 |
| AP03 | FY22 | 21 | 18 | 8/23/2021 | 9/5/2021 |
| AP03 | FY22 | 21 | 19 | 9/6/2021 | 9/19/2021 |
Table 1 - After
| Work ID | Work Completed | Accounting Period | Fiscal Year | Calendar Year | Payperiod Number |
| 112 | 6/29/2021 | AP01 | FY22 | 21 | 14 |
| 113 | 7/11/2021 | AP01 | FY22 | 21 | 14 |
| 114 | 7/13/2021 | AP01 | FY22 | 21 | 15 |
| 115 | 7/20/2021 | AP01 | FY22 | 21 | 15 |
4 Replies
Hi,
Try these calculated column formulas in Table1
CALCULATE(VALUES(Table2[Accounting Period]),FILTER(Table2,Table2[Start Date]<=EARLIER(Table1[Completed Date])&&Table2[End Date]>=EARLIER(Table1[Completed Date])))
CALCULATE(VALUES(Table2[Fiscal Year]),FILTER(Table2,Table2[Start Date]<=EARLIER(Table1[Completed Date])&&Table2[End Date]>=EARLIER(Table1[Completed Date])))
CALCULATE(VALUES(Table2[Calendar Year]),FILTER(Table2,Table2[Start Date]<=EARLIER(Table1[Completed Date])&&Table2[End Date]>=EARLIER(Table1[Completed Date])))
CALCULATE(VALUES(Table2[Payperiod ID]),FILTER(Table2,Table2[Start Date]<=EARLIER(Table1[Completed Date])&&Table2[End Date]>=EARLIER(Table1[Completed Date])))
If your table 2 has Work ID column, you can merge them.
