Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 |
Solved! Go to Solution.
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])))
I will try this! Thank you very much.
If your table 2 has Work ID column, you can merge them.
Unfortunatley, table 2 does not have the work ID
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
63 | |
59 | |
56 | |
38 | |
29 |
User | Count |
---|---|
82 | |
62 | |
45 | |
41 | |
40 |