Forum Discussion

Pablo7777's avatar
Pablo7777
New Member
3 years ago

Match 2 tables by multiple columns (dates issue)

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 I...
Need Help
Show and Tell
Tips and Tricks
johnt75's avatar
johnt75
Super User
3 years ago

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