Forum Discussion
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 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!
3 Replies
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
Do you have another solution where there are more than 1 row per project in costs for any given date?
What additional criteria would you use to determine which row to use ?
