Matrix values using two different date columns.

Hello,

I'm stuck, and hoping someone can assist please. I'm not even sure how to word the problem/scenario. 😕

I have a fact table with a date column of "target_date_completion" which my date table connects to (marked in red in screenshot). There is also a cost column which i need to sum up.

However, the fact table has a column called "date_completed", which is empty if the job is not yet completed. (Column is actually calculated column just copying from from a related table which is 1-to-1 relationship, see arrows in screenshot)

Majority of my dashboard is based around the target_date_completion. So for example we want to measure the number of jobs completed within the target_date_completion value (I got that solved). But I also need to calculate the cost column for each month as well. But this cost is to be done based on the date_completed column and not the target_date_completion column. I then need to be able to drop this cost aggregation into my matrix which IS basing the rest of the data around target_date_completion and so my slicers all work of that.

So somehow, I need 90% of my dashboard to work by target_date_completion. But the sums of cost to work to date_Completed.

Hope that makes sense because it exploded my head just trying to explain it.

thanks