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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors