2 tables with Different dates

[yazenbarakat123]

I have 2 tables , an Incident table and a Financials tables as shown below.    I'm trying to use these tables to create a Safety score  which is the  The Number of incidents that month / by the financials.. What im having troubles with is writing a dax measure to see if a month has an incident and to use its appropiate finanicials in the calcultion 

 

[yazenbarakat123]

It worked !!! Thank you @johnt75 

[johnt75]

Create a proper Date table and link that to both of your existing tables. Use the Date table in your visuals and your calculations

[yazenbarakat123]

Thank you for your Qucik response ! however creating 2 relationships to a Date table will automatically disable on of the relationships , created a date table and connected them on the Month as seen below not sure if this is best practice 

 

[johnt75 · Accepted solution]

No, you should link them on the Date field, so that it is a one-to-many relationship from Date to each of the other tables.

You can make both relationships inactive, so as to not interfere with the current relationship, and then use USERELATIONSHIP in your calculations.

It is not best practice to have many-to-many relationships, that can make for some very complicated DAX and some unexpected behaviours. It might be worth looking at changing your main relationship to one-to-many, possibly by building a bridge table between the two.