Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 - Befo...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    I will try this! Thank you very much.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I will try this! Thank you very much.