Add calculated fields based on date range

[krueb]

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

[Ashish_Mathur]

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

[krueb · Accepted solution]

I will try this! Thank you very much.

[mussaenda]

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

[krueb]

Unfortunatley, table 2 does not have the work ID