Hopefully this is the correct forum category. I’ve been trying all sorts of different ways to overcome my issue, but I’ve hit a very dark brick wall (maybe it’s the isolation!).
I have 2 separate fact tables, that have prices with very different date structures.
The PLATTS table has a date column with unique dates, but the CRU column has a date column with the same date for each week of the month e.g. March 2020 has data for 4 weeks, with the same date 01/03/2020.
If i was reporting CRU on it’s own, i’d be okay, but i am required to take 57% of the CRU value and 43% of another index price from the PLATTS table to create a new price. I’d like to report this new value by week.
I’m really stuck as to how I get this result…do i create a new table, is there a way to create a date field that will allow this type of cross calculation, or am i missing another option completely?
Any help would be much appreciated.