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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors