Hi all - I have tried to describe the situation to the best of my ability below, please let me know if you need additional details.
Each dataset has it's own date field attached to their respective measures, over a time series of 30 years, but they are sporadic days so do not match up between datasets.
I originally did a full outer join by project ID (my key field in each data set) and by the respective date of each set (lets call them date 1 and date 2). I then added a calculated field that said if date 1 was null, to retrieve date 2, else retrieve date 1. I called this a consolidated date and used it as my time series.
It worked in some cases however the two issues I had where:
1. The measure in the first dataset needed filtering by a dimension from the same dataset. This then filters the second measure (only when the dates have aligned and pulled the second measure onto the same line in the joined data).
2. The measures (at least the second measure) is duplicating when the project ID and dates match up.
I hope this was clear enough to explain the issue. I'm not able to post my datasets as it is protected by my firm but if my description isn't good enough I can try to mock up an example.
Thanks for the help all!
Instead of joining the 2 data sets, can you do a union of them?
And yes, it would be better to provide some mockup data and the expected result from the same.