Forum Discussion

QaziArfeen's avatar
QaziArfeen
Frequent Visitor
8 years ago
Solved

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 date...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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 )
BURAIDO's avatar
BURAIDO
Regular Visitor
8 years ago

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