Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

I will try this! Thank you very much.

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

I will try this! Thank you very much.

mussaenda
Super User
Super User

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

Anonymous
Not applicable

Unfortunatley, table 2 does not have the work ID

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.