cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
krueb Frequent Visitor
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 IDCompleted Date
1126/29/2021
1137/11/2021
1147/13/2021
1157/20/2021

 

Table 2

 

Accounting PeriodFiscal YearCalendar YearPayperiod IDStart DateEnd Date
AP01FY2221146/28/20217/11/2021
AP01FY2221157/12/20217/25/2021
AP02FY2221167/26/20218/8/2021
AP02FY2221178/9/20218/22/2021
AP03FY2221188/23/20219/5/2021
AP03FY2221199/6/20219/19/2021

 

Table 1 - After

 

Work IDWork CompletedAccounting PeriodFiscal YearCalendar YearPayperiod Number
1126/29/2021AP01FY222114
1137/11/2021AP01FY222114
1147/13/2021AP01FY222115
1157/20/2021AP01FY222115

 

1 ACCEPTED SOLUTION

Accepted Solutions
krueb Frequent Visitor
Frequent Visitor

Re: Add calculated fields based on date range

I will try this! Thank you very much.

4 REPLIES 4
mussaenda Member
Member

Re: Add calculated fields based on date range

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

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

 

Untitled.png

krueb Frequent Visitor
Frequent Visitor

Re: Add calculated fields based on date range

Unfortunatley, table 2 does not have the work ID

krueb Frequent Visitor
Frequent Visitor

Re: Add calculated fields based on date range

I will try this! Thank you very much.