cancel
Showing results for
Did you mean:
Highlighted
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

```Cost =
VAR Rate =
CALCULATE (
VALUES ( HourlyRates[Rates] ),
FILTER (
HourlyRates,
)
)
RETURN
ROUND ( Tasks[Duration] * Rate / 60, 2 )```
3 REPLIES 3
Super User

## Re: Calculate cost based on date ranges

```Cost =
VAR Rate =
CALCULATE (
VALUES ( HourlyRates[Rates] ),
FILTER (
HourlyRates,
)
)
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 BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 157 members 2,037 guests
Recent signins: