Measure by week, month, and year

[SignorSoprano]

Hi, all,

I know there are posts related to this same topic, but I couldn't find one pertaining to my specific question.

I am calculating employee utilization. My date table has all of the usual columns (date, year, quarter, month, end of week date, and other related columns), so I have the ability to drill [down] my visuals to year, quarter, month, and week.

All of my data, which are the hours an employee charged to a project, has the week ending date atttached.

For each employee, I am showing the available hours, billable hours, overhead hours, and pto hours and utilization. When the visual is drilled down to week ending date, it shows this per week; when it is drilled down to month, it shows this per month; etc...

The way I am calculating the utilization is (billable hours / available hours). My company wants to cap the available hours to 40, but billable hours can be more than 40, so that the utilization can be > 100%.

My problem is this: if I want to cap the available hours to 40 per week, how can I drill up to month and get the hours for the month capped at 40 per week?

Here are my measures currently:

Utilization = [Billable Hours] / [Available Hours]

Billable Hours =

  CALCULATE(

    SUM('BST'[Hours/Units])

    , FILTER(

        'BST'

        , [Charge Type] = "B"

    )

  )

Available Hours =

  CALCULATE(

    SUM('BST'[Hours/Units])

    , FILTER(

        'BST'

        , [Project Number] <> "11111111"

    )

  )

where "B" represents billable hours (I guess that was obvious) and "11111111" is the project to which absences (vacation, sick, bereavement, leave without pay, etc.) are charged.

How can I limit the Available Hours to 40 per week but also get the measures to be correct when the context of the visual is something other than the week? In other words, when I want to drill up to month, how can I limit the Available Hours measure to be 40 per week per employee?

Keep in mind that the hours a person may have available for the week may be more or less than 40. Some people only work 32 or 24 hours per week. However, I do not know each person's max hours for the week. The only way I can calculate the available hours is by adding up all of the charges for the week and subtracting the absence hours charged. If this is more than 40, I want to cap it to 40. If it is less than 40, I want to keep it at however many hours that it totals to.

[technolog]

You want to cap the available hours at 40 when calculating the utilization, but you also want to ensure that when you're looking at a month's worth of data, it's essentially the sum of the capped weeks. So, if an employee worked 50 hours every week for 4 weeks, instead of showing 200 hours for the month, you'd want to show 160 hours (40 hours capped x 4 weeks).

To achieve this, you can modify your Available Hours measure to check the context in which it's being used (week, month, etc.) and then apply the logic accordingly.

Here's a way to do it:

Available Hours Capped =
VAR WeeklyHours =
CALCULATE(
SUM('BST'[Hours/Units]),
FILTER(
'BST',
[Project Number] <> "11111111"
)
)
VAR CappedHours =
IF(WeeklyHours > 40, 40, WeeklyHours)
RETURN
IF(
HASONEVALUE('DateTable'[end of week date]),
CappedHours,
SUMX(
VALUES('DateTable'[end of week date]),
CappedHours
)
)
Here's what's happening:

We first calculate the weekly hours for the current context using the WeeklyHours variable.
We then cap these hours at 40 using the CappedHours variable.
Finally, we check if the current context is at the week level using HASONEVALUE('DateTable'[end of week date]). If it is, we simply return the capped hours. If it's at a higher level (like month or year), we use SUMX to iterate over each week in the current context and sum up the capped hours for each week.
Now, you can use this new Available Hours Capped measure in your utilization calculation:

Utilization = [Billable Hours] / [Available Hours Capped]
This should give you the desired behavior of capping the available hours at 40 per week, regardless of the visual's context.