Matrix values using two different date columns.

[RobThrive]

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

[amitchandak · Accepted solution]

@RobThrive , You can create a date table and join it with both dates. Keep the most used on as active and another one inactive. You can use userelation in a measure to activate a relation

refer

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[RobThrive]

@amitchandak , you, good sir have just elevated my DAX game to the next level!

Implementing this change to my existing measure was so fast and simple once I read those articles to understand it.

Thank you so much.