cancel
Showing results for
Did you mean:
Frequent Visitor

## 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Calculate cost based on date ranges

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 3
Super User

## Re: Calculate cost based on date ranges

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 )```
Frequent Visitor

## Re: Calculate cost based on date ranges

Thank you @Zubair_Muhammad it worked perfectly

Visitor

## Re: Calculate cost based on date ranges

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...

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### PBI Community Highlights

Check out what's new in the Power BI Community!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 203 members 1,901 guests
Recent signins: