I have an issue with granularity with dates en projectinformation. I have a table with projectheader information with monthly pipeline value snapshots and I have an table with Invoices related to that project. I don't always have an header date or an invoice date but what I need is one calendar to select dates in both tables.
I know this can be done with Date bridges and GenericKeys but can't figure it out...need some advice or sample script.
Can't join them, I miss dates.If I want to know invoices of a project but i don't have a date in the header table (and that's possible because we created snapshots after a certain period) then the information is incomplete.
You can add a Master Calendar with Two different Date Fields with same value as below (Keep the names same as Date fields from Invoices and Projectheader tables). This will join the master calendar table with other two tables.
//To be linked with Projectheader table's Date field
DateKey AS Date,
//To be linked with Invoicestable's Invoice Date field
DateKey AS [Invoice Date]
This is draft version of solution; to further fine tune it you can implement the suggestion mentioned by stalwar1.