Cap Sum by Day

[db_programmer]

I am not new to DAX but I’m not an expert either.  I need some help, please, developing a calculation and I’m not sure the best approach.

My data model captures data about utilization of resources.  There are many resources and each resource has both available time (denominator) and time that it is used (numerator).   One of the main measures is basically numerator/denominator (utilization rate %):

[Utilization Rate %]:=

VAR Num =

    [Hourly Utilization Adjusted Open-Normal Used Time (minutes)]

VAR Den =

    [Hourly Utilization Adjusted Open-Normal Available Time (minutes)]

VAR NewNum = IF(ISBLANK(Num) && Den > 0,0,Num)

RETURN

    DIVIDE (

        NewNum

        ,Den

        ,BLANK ()

    )

The granularity of the data is user and hour.  That is, if a user makes use of the resource for two hours there are two numerator records.  If two users make use of the resource across two different hours, there are three records:

Sometimes the resource is utilized when it is “not available” and I have a calculation to report on that as well, e.g., [Hourly Utilization Adjusted Closed-Time Used Time (minutes in Business Hours)], and this gets added to the numerator:

Hourly Utilization Adjusted Closed-Time Used Time (minutes in Business Hours)]:=

CALCULATE(

    SUMX(

        FILTER(

            'Hourly Utilization Adjusted',

                && ( ( NOT 'Hourly Utilization Adjusted'[Hour of Day] IN { 7, 8, 9, 10, 11, 12, 13, 14, 15, 16 } )

                || ( RELATED( 'Calendar'[Is Holiday 01] ) = 1 )

                || ( RELATED( 'Calendar'[Weekend 01] ) = 1 ) )

        ),

        'Hourly Utilization Adjusted'[Duration]

    )

)

[Utilization Rate Adjusted %]:=

VAR Num =

    [Hourly Utilization Adjusted Open-Normal Used Time (minutes)]

    + [Hourly Utilization Adjusted Closed-Time Used Time (minutes in Business Hours)]

VAR Den =

    [Hourly Utilization Adjusted Open-Normal Available Time (minutes)]

VAR NewNum = IF(ISBLANK(Num) && Den > 0,0,Num)

RETURN

    DIVIDE (

        NewNum

        ,Den

        ,BLANK ()

    )

  In other words, treat the time as if it were available even though it was not.  Specifically, if the resource is utilized during normal business hours, even though a resource is marked “not available”, we count the utilization as if it were available.  That part works perfectly.

The problem I am running into is we have some extended hours where the business would also like to count the unavailable resources but based on parameters.  Specifically, there are four (4) resources that ARE available during extended hours at the end of the day, hours 17 and 18, resulting in 480 minutes of available time between 5pm and 7pm.   For the other resources that are NOT available during those extended hours, those minutes of utilization should also count as long as they aggregately do not exceed a combined 480 minutes per day.  Basically, unlike normal business hours where ALL unavailable resources count, during extended hours, unavailable resources count, but only up to a certain threshold. 

That is the part that I think is tricky.  If the pivot grid is sliced by date only, I could just filter my calculation based on the hour of the day and add up the closed time to see if it exceeds 480 minutes, and use an if statement to cap it.  The problem is if the pivot grid is sliced by hour of day or by resource or anything else, the 480-minute calculation would not work.

For the normal business hours, the calculation that brings in the unavailable numerator works at any granularity (i.e., from hour on up to year or higher):

How can I calculate the numerator, capping it off at 480 minute per day and make sure it is accurate at other granularities besides date?

The only approach I have conceptualized might work is to create a calculated table that summarizes the 5p to 7p usage by date, as an intermediate step for the rest of the calculation.  It seems like I need to somehow check the summary level in my calculation to see if the summary is by day or higher.  I’m not sure how to handle robustly the different ways the data could be sliced, e.g., day, week, month, year, etc., plus resource, location (the data has different locations), etc.

Note:

• Ideally, the 480-minute threshold would come from the data itself based on how many minutes/resources are available between 5pm and 7pm, but I am okay with hard-coding it as well if it simplifies the overall solution. 
• I’m okay with the calculation not using the cap if the summary level is lower than day.  For example, if summarized by hour, go ahead and give credit for all unavailable utilization, even if it exceeds 480 minutes.  The important calculation is when it is by date or higher.

[db_programmer]

Here is some sample data:

Date	Month-Year	Hour	Resource	Duration Used
20230101	January 2023	16	1	60
20230101	January 2023	16	2	60
20230101	January 2023	16	3	60
20230101	January 2023	16	4	60
20230101	January 2023	16	5	60
20230101	January 2023	17	1	59
20230101	January 2023	17	2	60
20230101	January 2023	17	3	60
20230101	January 2023	17	4	60
20230101	January 2023	17	5	1
20230101	January 2023	18	1	60
20230101	January 2023	18	2	60
20230101	January 2023	18	3	60
20230101	January 2023	18	4	60
20230101	January 2023	18	5	60
20230102	January 2023	16	1	60
20230102	January 2023	16	2	60
20230102	January 2023	16	3	60
20230102	January 2023	16	4	60
20230102	January 2023	16	5	60
20230102	January 2023	17	1	60
20230102	January 2023	17	2	60
20230102	January 2023	17	3	60
20230102	January 2023	17	4	60
20230102	January 2023	17	5	1
20230102	January 2023	18	2	60
20230102	January 2023	18	3	60
20230102	January 2023	18	4	60
20230102	January 2023	18	5	60
20230102	January 2023	16	1	60

Here are the expected results:

[lbendlin · Accepted solution]

you sample data and the expected outcome don't agree.

Anyway, here's my proposal.  You could hide the extra "cap"  columns or ignore them.

[db_programmer]

Beautiful, thank you!  I use an uncommon combo of tools (Tableau, Tabular Editor, and SSAS) - no Power BI...yet.  I'll plug this into TE3 and give it a shot, but your results are spot on.  Sorry about the sample data being off.  Nothing like bad data to throw off the example, but glad you caught it.  

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523