Calculating Deviation in hours using two different Date references

Hello everyone,

Im having some issues relating filtering results with dates. The following image shows how the data model is setup:

The problem here is the fact that I've got a matrix in a page which uses "date" from the table Date_dim and a measure that requires to use the "Contract Start Date" and "Contract End Date".  Since there's no relation between the tables Date_dim and TblContracts, the end result is a very slow calculating matrix, resulting even sometimes on an error message saying that there were not enough resources to perform the calculations within the measure.

The goal with the matrix is to calculate the deviation of the amount of hours a colaborator uses on a given task. This means that we'll need to calculate the amount of billable, non-billable and contracted hours on each task. To further add to the complexity of the calculation here, the values required to calculate the amount of hours each colaborator used on a certain task is stored in the Timeworked table.

This is the measure im using to calculate hours within the time frame given "Contract Start Date" and "Contract End Date":

Deviation =

var _firstDate = FIRSTDATE(TblContracts[Contract Start Date])

var _lastDate = LASTDATE(TblContracts[Contract End Date])

var currentFilterDate = MAX ( Date_dim[date] )

var billableHours = CALCULATE (

SUM(Timeworked[Total_time_in_hours]),

Timeworked

, CROSSFILTER ( TblContracts[SNOW reporting ticket - billable ], Overview[project_task_number], BOTH )

, CROSSFILTER ( Timeworked[task_dim_id], Overview[project_task_dim_id], Both)

, FILTER(Date_dim,Date_dim[date] >= _firstDate && Date_dim[date] <= _lastDate)

, FILTER(Overview, ISBLANK(Overview[project_task_number]) == False)

)

VAR nonBillableHours =

CALCULATE (

SUM ( Timeworked[Total_time_in_hours] ) ,

Timeworked

, USERELATIONSHIP(TblContracts[SNOW reporting ticket - NOT billable ], Overview[project_task_number])

, CROSSFILTER ( TblContracts[SNOW reporting ticket - NOT billable ], Overview[project_task_number], Both )

, FILTER(Date_dim,Date_dim[date] >= _firstDate && Date_dim[date] <= _lastDate)

, FILTER(Overview, ISBLANK(Overview[project_task_number]) == False)

)

VAR totalContractedHours =

CALCULATE (

SUM(TblContracts[Hours/Month SM Contracted Hours])

, FILTER(All(Date_dim),currentFilterDate >= _firstDate && currentFilterDate <= _lastDate)

, FILTER(Overview, ISBLANK(Overview[project_task_number]) == False)

, CROSSFILTER ( TblContracts[SNOW reporting ticket - billable ], Overview[project_task_number], BOTH )

, CROSSFILTER ( Timeworked[task_dim_id], Overview[project_task_dim_id], Both)

)

return totalContractedHours - (billableHours + nonBillableHours)

------------------------------------------------------------------------------------------------------------------------------------------

This measure is being used in the Values field of the matrix, while the dates from Date_dim are being used as the Columns. One of the solutions i've thought about would be creating a new table that would link Date_dim to TblContracts, however im not sure how to tackle the issue here.

Thank you very much in advance!