Forum Discussion
Connect two tables with complex requirements
Hi team,
I have two tables: period, and schedule
the "Period" table has the below features:
the "Schedule" tale has the below features:
How can I make a relationship between both of these tables based on date? My main problem is that the "Period" table gives us a range of dates so I can't make a relationship straight away.
I call this issue complex because I'm relatively new to this tool!
5 Replies
Hi jfernand
The period table does it refer to your weeks from the 1st of Jan of 2015 ?
Also let me know what you plan to achieve by linking the two tables.
Cheers
CheenuSing
Hi Cheenu,
thank you for your prompt response.
Yes, the table does refer to weeks from the 1st of January. Since there isn't any relevant data before the 1st of April - the date the business started - I made a query to the "Period"table so that I would only get results after that date.
My objective is to connect both tables so that I can build reports jointly. I discussed with my peers the idea of using formulas to retrieve the week from the "Schedule" table but the requirement is that we use what we have in our database.
I'm not able to confirm if if I used formulas to retrieve the period from "Schedule" I would get the same range as in the "Period" table so, in their point of view, the safest way is to connect these two tablesHi jfernand,
You could create a [Period] column in 'Schedule' table, then, create relationship based on the common column [Period].
Period = YEAR('Schedule'[trans_date])& WEEKNUM('Schedule'[trans_date])
Regards,
Yuliana Gu
