Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rogerpoggi
Regular Visitor

COMPLEX WORKDAYS CALCULATION

Hello everyone,

I oversee a table that captures all the support requests submitted to our team, each demanding a response based on its assigned priority level:

  • Urgent: 8 hours
  • High: 24 hours
  • Average: 72 hours
  • Low: 26 days

Within this dataset, we utilize a "Priority" column to indicate the urgency of each ticket and a "Creation_Date" column to document the date, time, and GMT when the ticket was opened. To refine our metrics, I'm seeking assistance in adding two new columns and creating an "Adjusted_Date" column. This adjustment takes into account the following parameters:

  • Working hours from Monday to Friday extend from 8 am to 5 pm.
  • On Saturdays, support is available from 8 am to 12 pm.
  • Sundays and holidays are considered non-working days.

PS¹: The time format is based on a 24-hour duration.

 

The column "End_Date" represents the date on which the request was closed.

 

Final objective: this calculated metric serves as a crucial measure to evaluate the timeliness of our team's responses to reported issues. Your collaboration in implementing these adjustments will significantly contribute to our ability to monitor and enhance our response times. Thank you.

 

See the link to the files (I uploaded them to a public folder on Google Drive): https://drive.google.com/drive/folders/141f6ZHZ4qhgi7lCcgecqJo8dTU0aAUXW?usp=sharing

3 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

 

 

 

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?

 

lbendlin_0-1701397094614.png

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 )

View solution in original post

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

View solution in original post

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

 

lbendlin_0-1701979500808.png

 

View solution in original post

7 REPLIES 7
rogerpoggi
Regular Visitor

1) Where is your table with the list of holidays? 

- Please see the link to the list of holidays (I uploaded them to a public folder on Google Drive): https://docs.google.com/spreadsheets/d/1IEhgI--mDPwfHgd7Gv1ae_YWUq88VTPcsGCTHfgFC-A/edit?usp=sharing

 

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

Tickets raised and closed outside working hours must be realocated to the next workday/hour. 

 

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

By the minute.

 

Thanks!

 

 

 

Tickets raised and closed outside working hours must be realocated to the next workday/hour. 

 

I don't know what that means. Can you please explain?

 

lbendlin_0-1701905224383.png

 

The guiding principle of my logic is that if a person is working outside of regular working hours, that is their individual circumstance. It would be unrealistic to expect the same from other workers.

 

For instance, if a person opens a ticket at 5:35 pm on a Friday, it should be treated as if they opened it at 8 am on Saturday. The same logic applies to closed tickets. If a person closes a ticket at 8:45 pm on a Monday, it should be considered as closed at 8 am on Tuesday.

ok - my proposal should cover that.

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.

 

lbendlin_0-1701979500808.png

 

lbendlin
Super User
Super User

 

 

 

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?

 

lbendlin_0-1701397094614.png

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 )

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors