cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Add calculated fields based on date range

I will try this! Thank you very much.

4 REPLIES 4
Member

## Re: Add calculated fields based on date range

If your table 2 has Work ID column, you can merge them.

Super User

## Re: Add calculated fields based on date range

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])))`

Frequent Visitor

## Re: Add calculated fields based on date range

Unfortunatley, table 2 does not have the work ID

Frequent Visitor

## Re: Add calculated fields based on date range

I will try this! Thank you very much.