cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
QaziArfeen Frequent Visitor
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

hourlyrates.png

 

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

Tasks.png

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.

Cost.png

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

Re: Calculate cost based on date ranges

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

Re: Calculate cost based on date ranges

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

Re: Calculate cost based on date ranges

Thank you @Zubair_Muhammad it worked perfectly

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

Helpful resources

Announcements
Virtual Launch Event

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.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 203 members 1,901 guests
Please welcome our newest community members: