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 )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
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 )