Multiple facts different dates but need to select data
hopefully someone can help with this extremely complex question (it may well not be able to be answered).
i currently have 2 fact tables which both contain different date fields however there are some keys that are common in both fact tables (i will try to draw something to explain this) however not all key fields will be in both fact tables.
what i want to do is in theory combine the 2 fact tables and if i select a date from fact 1 i want to keep the association to fact 2.
i select '01/01/2019' from date field 1 i want to show all corresponding values in fact 1 & fact 2 that link based on the key1 field. so in the example shown i select key1='ABC123' and date1='01/01/2019' which sums the value £100 from field 1, but i also want to show the sum of the date1 = '01/02/2019&01/03/2019' and date3='01/07/2019' because these have the same key1 but a different date. so there is still assocation between the key1 field but different by date. so the sum of field 1 = £300 and sum of field 2 = £100 even though i have selected 01/01/2019
as i say i dont know if this is possible or if i need to go down the link table route but based on many dimesion table and 2 fact tables and many calendars this seems like the data model could become very slow with c200million rows in there. i am just looking to keep the association even though im selecting a date