How to Measure User-Defined `X` Days Considering Working Hours in Issue Tracking Tool

[Rockz]

Dear Champions,

I am developing an issue tracking tool where users can define a target value `X` (in days) to measure issue processing times. The challenge is to measure this while considering only working hours (Monday to Friday, 08:00 to 16:30).

**Requirement:**

1. Users input `X` days.
2. Calculate processing times considering only working hours.
3. Determine if processing times meet the `X` days target.

Could anyone provide guidance or examples on how to accurately measure `X` days in terms of working hours?

Thank you for your assistance!

This version is concise and directly addresses the need to calculate `X` days based on user input while considering working hours.

[v-heq-msft · Accepted solution]

Hi @Rockz ,

Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create measures

Duration = 
VAR StartTime = 
IF(
    SELECTEDVALUE('Table'[Start Time]) <= TIME(8,0,0),
    TIME(8,0,0),
    SELECTEDVALUE('Table'[Start Time])
)
VAR EndTime = 
IF(
    SELECTEDVALUE('Table'[End Time] ) >= TIME(16,30,0),
    TIME(16,30,0),
    SELECTEDVALUE('Table'[End Time])
)
RETURN
IF(
    WEEKDAY(SELECTEDVALUE('Table'[Date])+ 1) <= 5,
    HOUR(EndTime - StartTime) + MINUTE(EndTime - StartTime)/60,
    0
)

Total Days = 
VAR _t = 
SUMMARIZE(
    'Table',
    'Table'[Date],
    'Table'[Start Time],
    'Table'[End Time],
    "Duration",[Duration]
)
RETURN
SUMX(_t,[Duration])/24

Create a numeric parameter

Create a KPI visualization

Fianl output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly