Forum Discussion
Calculate cost based on date ranges
I have an issue which I need help with.
I have a table "Hourly Rates" which has hourly rates for employees with start and end date. This table will track all the hourly rates with start and end dates
Example
I have Tasks table which tracks tasks done by employees and how much time it took them. This table also has task performed date
Example
Rates in Hourly rates are per hour and Duration in Tasks table in minutes.
We already have a relation between Employee Code and all the date columns are formatted. Above example is for illustration only. What I am looking for is the following in PowerBI. It does not matter if we create a custom column or a measure or a new table all together.
It is very obvious that I am looking to calculate COST depending on the date the task was performed and what were the hourly rates when the task was performed.
Hi QaziArfeen
Add this calculated column in Tasks Table to get the Cost
Cost = VAR Rate = CALCULATE ( VALUES ( HourlyRates[Rates] ), FILTER ( HourlyRates, Tasks[Employee Code] = HourlyRates[Employee Code] && Tasks[Task Performed On] >= HourlyRates[Start Date] && Tasks[Task Performed On] <= HourlyRates[End Date] ) ) RETURN ROUND ( Tasks[Duration] * Rate / 60, 2 )
3 Replies
Hi QaziArfeen
Add this calculated column in Tasks Table to get the Cost
Cost = VAR Rate = CALCULATE ( VALUES ( HourlyRates[Rates] ), FILTER ( HourlyRates, Tasks[Employee Code] = HourlyRates[Employee Code] && Tasks[Task Performed On] >= HourlyRates[Start Date] && Tasks[Task Performed On] <= HourlyRates[End Date] ) ) RETURN ROUND ( Tasks[Duration] * Rate / 60, 2 )Thank you Zubair_Muhammad it worked perfectly
I have a similar problem and the solution given seems fine. But my question is the following:
How did you join both tables? It seems that it's a many-to-many relationship if you have the Employee Code several times in both tables...
