COMPLEX WORKDAYS CALCULATION

Sundays and holidays are considered non-working days.

Where is your table with the list of holidays?

How are you planning to handle tickets that were raised outside of the working hours, and/or were closed outside of working hours?

What granularity do you need?  Hourly? Half Hourly?  By the minute?

Working Hours =
VAR a =
    GENERATESERIES (
        [dataDeCriacao] * 1440,
        COALESCE ( [dataDePronto], TODAY () ) * 1440 - 1
    )
VAR b =
    ADDCOLUMNS (
        CALENDAR ( [dataDeCriacao], COALESCE ( [dataDePronto], TODAY () ) ),
        "Start", IF ( WEEKDAY ( [Date], 2 ) < 7, 480 ),
        "End",
            IF ( WEEKDAY ( [Date], 2 ) < 6, 1060, IF ( WEEKDAY ( [Date], 2 ) = 6, 720 ) )
    )
VAR c =
    SELECTCOLUMNS (
        GENERATE (
            b,
            GENERATESERIES ( [Date] * 1440 + [Start], [Date] * 1440 + [End] - 1 )
        ),
        "Value", [Value]
    )
RETURN
    DIVIDE ( COUNTROWS ( INTERSECT ( a, c ) ), 60, 0 )

It's working perfectly, but I just realized that I forgot one important thing! 😥

I've left out the last table sent (beginners' mistake, I'm sorry!), the 'kanbanStatus' column which keeps track of the ticket's status:

• Pendente (Pending)
• Em andamento (In progress)
• Aguardando aprovação de orçamento (Awaiting budget approval)
• Feito (Done)

This information is crucial for the ticket study:

When the ticket is 'pending' or 'in progress,' the clock is ticking normally. However, when the ticket is set to 'Awaiting budget approval,' it is put on hold, and so the time should stop. 'Done' is when the ticket is solved.

You can view the RAW database through the following link: https://drive.google.com/file/d/1jA4DG9ZS0hp_5sCnj-sCD4HxhTAP9qpC/view?usp=sharing

You did include that column. Easy enough to add a filter yourself.