Match 2 tables by multiple columns (dates issue)

[Pablo7777]

Hi,
I have a two tables like below.

I need to calculate Total Costs for every Project. (hours spent * hourly cost)

To do that I have to match these tables by multiple columns (Project ID + execution date must be between 'date from' and 'date to').
The second part (dates matching) is the main issue for me.
I tried usind TREATAS function, but I don't know how to include condition with the dates.
The solutions must be in DAX.

I attach the sample pbix file:
https://drive.google.com/file/d/17Ik6TosY5m1lLQ_MmLJSOcwevJGmJ3Sr/view?usp=sharing

Any help would be much appreciated!

[Pablo7777]

Do you have another solution where there are more than 1 row per project in costs for any given date?

[johnt75]

What additional criteria would you use to determine which row to use ?

[johnt75]

You can use

Total Costs = SUMX(
	'TimeSpent',
	VAR CurrentProject = 'TimeSpent'[Project ID]
	VAR HoursSpent = 'TimeSpent'[Hours Spent]
	VAR CurrentDate = 'TimeSpent'[Execution date]
	VAR HourlyCost =
		SELECTCOLUMNS(
			FILTER(
				'Costs',
				'Costs'[Project ID] = CurrentProject
					&& 'Costs'[Date from] <= CurrentDate
					&& 'Costs'[Date to] >= CurrentDate
			),
			"@value", 'Costs'[Hourly Cost]
		)
	RETURN
		HourlyCost * HoursSpent
)

as long as you only have 1 row per project in costs for any given date