Time period calculation for employee costs.

[Kristers]

Hello. I have come across a problem that I can't solve, and I haven't found any discussions on this topic yet. I need to create a calculation where the cost of an employee is calculated based on a period of time, what cost rate he has in a given period. When I filter, each month separately, then I get the required value, when I filter several months together, then it is calculated by the highest value that is specified. In this case it is after maxx calculation, it even happens after min. I can't use avarage here because it will miscalculate the data.

For example, an employee in January had a cost price of 25, but in February 30.
When I filter January and February it will take value 30 and make all calculations based on that value.
But I need to calculate each month with its own value and get the total based on each month's value.

January

February

Filter January and February

Total cost should be = 4400 + 5040 = 9440 but I have 10320, because calculation use only cost per hours 30.

I have measure:

EmployeeCosts =

VAR CurrentMonthEnd = ENDOFMONTH(LASTDATE('DateTable'[Date]))

VAR EmployeeID = SELECTEDVALUE('Table1'[EmployeeId])

VAR FilteredRateChanges =

    CALCULATETABLE(

        'Table1',

        'Table1'[EmployeeId] = EmployeeID &&

        'Table1'[Cost rate change start date] <= CurrentMonthEnd

    )

VAR LatestRateChange =

    CALCULATE(

        MAX('Table1'[Cost rate change start date]), 

        FilteredRateChanges

    )

VAR LatestRate =

    CALCULATE(

        MAX('Table1'[CostPerHour]),

        FILTER(

            FilteredRateChanges,

            'Table1'[Cost rate change start date] = LatestRateChange

        )

    )

RETURN

IF(

    NOT ISBLANK(EmployeeID),

    LatestRate,

    BLANK()

)

I get data from excel:

EmployeeId	CostPerHour	Cost rate change start date
KRBR	25	01/01/2024
KRBR	30	01/02/2024

[bhanu_gautam · Accepted solution]

@Kristers , Create a measure to calculate the cost for each month

DAX
MonthlyEmployeeCost =
VAR CurrentMonthStart = STARTOFMONTH('DateTable'[Date])
VAR CurrentMonthEnd = ENDOFMONTH('DateTable'[Date])
VAR EmployeeID = SELECTEDVALUE('Table1'[EmployeeId])
VAR FilteredRateChanges =
CALCULATETABLE(
'Table1',
'Table1'[EmployeeId] = EmployeeID &&
'Table1'[Cost rate change start date] <= CurrentMonthEnd
)
VAR LatestRateChange =
CALCULATE(
MAX('Table1'[Cost rate change start date]),
FilteredRateChanges
)
VAR LatestRate =
CALCULATE(
MAX('Table1'[CostPerHour]),
FILTER(
FilteredRateChanges,
'Table1'[Cost rate change start date] = LatestRateChange
)
)
VAR HoursWorked =
CALCULATE(
SUM('Table1'[HoursWorked]),
'DateTable'[Date] >= CurrentMonthStart && 'DateTable'[Date] <= CurrentMonthEnd
)
RETURN
IF(
NOT ISBLANK(EmployeeID),
LatestRate * HoursWorked,
BLANK()
)

Create a measure to sum up the monthly costs:

DAX
TotalEmployeeCost =
SUMX(
VALUES('DateTable'[MonthYear]),
[MonthlyEmployeeCost]
)

Use this measures in visuals

[bhanu_gautam · Accepted solution]

@Kristers , It is likely due to the context in which the VALUES function is being evaluated. When you filter by multiple employees, the context changes, and the VALUES function might not return the expected results. 

Try using SUMMARIZE

TotalEmployeeCost =
SUMX(
SUMMARIZE(
'DateTable',
'DateTable'[MonthYear],
'Table1'[EmployeeId],
"MonthlyCost", [MonthlyEmployeeCost]
),
[MonthlyCost]
)

[Kristers]

You are the best! Thank you, the solution works! 🙂

Best regards, 

Kristers

[bhanu_gautam · Accepted solution]

@Kristers , Create a measure to calculate the cost for each month

DAX
MonthlyEmployeeCost =
VAR CurrentMonthStart = STARTOFMONTH('DateTable'[Date])
VAR CurrentMonthEnd = ENDOFMONTH('DateTable'[Date])
VAR EmployeeID = SELECTEDVALUE('Table1'[EmployeeId])
VAR FilteredRateChanges =
CALCULATETABLE(
'Table1',
'Table1'[EmployeeId] = EmployeeID &&
'Table1'[Cost rate change start date] <= CurrentMonthEnd
)
VAR LatestRateChange =
CALCULATE(
MAX('Table1'[Cost rate change start date]),
FilteredRateChanges
)
VAR LatestRate =
CALCULATE(
MAX('Table1'[CostPerHour]),
FILTER(
FilteredRateChanges,
'Table1'[Cost rate change start date] = LatestRateChange
)
)
VAR HoursWorked =
CALCULATE(
SUM('Table1'[HoursWorked]),
'DateTable'[Date] >= CurrentMonthStart && 'DateTable'[Date] <= CurrentMonthEnd
)
RETURN
IF(
NOT ISBLANK(EmployeeID),
LatestRate * HoursWorked,
BLANK()
)

Create a measure to sum up the monthly costs:

DAX
TotalEmployeeCost =
SUMX(
VALUES('DateTable'[MonthYear]),
[MonthlyEmployeeCost]
)

Use this measures in visuals

[Kristers]

Hi, @bhanu_gautam 

Maybe you can help me a little with this same issue. Everything is working fine, and the calculations are correct, but I've encountered something I didn’t notice at first. As soon as I filter by one person, the Total result is displayed in the matrix, but when there are multiple people, I get an empty field instead of a Total.

From what I’ve observed, it happens because in the function -

TotalEmployeeCost = SUMX( VALUES('DateTable'[MonthYear]), [MonthlyEmployeeCost] )
When VALUES('DateTable'[MonthYear]) is being used. Is there a way to fix this to get the Total value? 

Best regards, Kristers

[Kristers]

Hello.

Thank you so much for your help, this solution helped to solve the problem.

Best regards, Kristers