How to calculate employee utilization % that adjusts with the timeframe adjusted by slicer

[TMN_help]

I would like to calculate Employee Utilization % (SUM of Hrs within a timeframe divided by NormalWeeklyHours) with data below:

FactTable

EmployeeNumber	BusinessDate	Hrs	DimTable.NormalWeeklyHours
123	9/26/2022	8	40
123	9/27/2022	8	40
123	10/3/2022	8	40
123	10/4/2022	8	40
123	10/5/2022	8	40
123	10/11/2022	10	40
123	10/12/2022	10	40
123	10/13/2022	10	40
234	10/3/2022	14	38
234	10/5/2022	12	38
234	10/6/2022	12	38
345	9/28/2022	10	20
345	10/5/2022	10	20
345	10/11/2022	12	20
456	10/13/2022	8	blank
567	10/11/2022	8	40
567	10/12/2022	8	40

DimTable

EmployeeNumber	NormalWeeklyHours
123	40
234	38
345	20
456	blank
567	40

There might be some rows with no value for NormalWeeklyHours. I have Merged the tables to get all the info in ‘FactTable’, but I’m not sure if that’s helpful.

In the visual I would like to include a slider with Business Dates to allow the users to adjust the time frame so the formula has to include a calculation to adjust NormalWeeklyHours with the time period since it is hours per week or 7 days; e.g. days between MIN BusinessDate and MAX BusinessDate is n, then NormalWeeklyHours have to be prorated by n/7

Result visual should look like this:

a)

EmployeeNumber	Total Hrs	NormalWeeklyHrs	Utilization
123	24	40	60%
234	38	38	100%
345	10	20	50%

Example 1: Time frame n=7

b)

EmployeeNumber	Total Hrs	NormalWeeklyHrs	Utilization
123	54	40	59.23%
234	38	38	53.85%
345	32	20	86.15%
456	8	blank	blank
567	16	40	21.54%

Example 2: Time frame n=13

[Anonymous · Accepted solution]

Hi @TMN_help ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a date dimension table and apply the date field on the slicer

2. Create two measures to get the Utilization:

Total Hrs = SUM('FactTable'[Hrs])

Utilization = 
VAR _normalwhours =
    CALCULATE (
        MAX ( 'DimTable'[NormalWeeklyHours] ),
        FILTER (
            'DimTable',
            'DimTable'[EmployeeNumber] = SELECTEDVALUE ( FactTable[EmployeeNumber] )
        )
    )
VAR _days =
    COUNTROWS (
        DATESBETWEEN ( 'Date'[Date], MIN ( 'Date'[Date] ), MAX ( 'Date'[Date] ) )
    )
RETURN
    DIVIDE ( [Total Hrs], DIVIDE ( _normalwhours * _days, 7 ) )

If the above ones can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[TMN_help]

This is what I meant, I just didn't know what should those 2 VAR look like. Thank you for your help, it worked!

[Anonymous]

Hi @TMN_help ,

Thanks for your feedback. The variable _normalwhours is used to get the normal work hours for per user from the dimension table, and the variable _days is used to get the number of days between the selected min date and selected max date on the slicer. 

Best Regards