Calculating Required Completion Time/Date based on...

javirmerino · ‎01-24-2020

Hi Guys, i'm hoping one of you geniuses (Genii?) will be able to help with an query i need to construct...

I've got a table full of ticket requests, each with a predetermined SLA, that i need to use to calculate the required completion time and determine whether it was actioned in time or not.

For example, ID 1234 (SysAid Extract.[ID]) was logged at 08:57 on Monday as Urgent (SysAid Extract.[Urgency]). As 08:57 is before working hours then the start time should be 09:00 by default on the same day, and a 4-hour completion time applied based on the Urgent priority, giving a completion time of 13:00. Similarly, any records received after 17:00 hours should default to 09:00am the preceeding day.

I'm also considering adding a holding table for Bank Holidays (TblHolidays or similar) and any internal days off (conferences, stand-down days etc) that can be linked so these dates are also excluded from the days when calculating required completion time and dates.

I've noticed that the DimDate table also has an IsHoliday boolean column that can be referred to also exclude any holiday days when calculating required completion time and dates.

The final complexity to this would be that a required completion date and time (SysAid Extract.[Required Completion Date]) can be added to over-ride the priority completely.

I've attached a screenshot of the table model below for reference;

Model Diagram

v-lid-msft · ‎01-26-2020

Hi @javirmerino ,

We can try to create a calculated column using following dax to meet your requirement:

Required Completion Date =
VAR d = [Date]
VAR t = [Time]
VAR isWorkDay =
    CALCULATE (
        COUNTROWS ( 'Dim_Date' ),
        FILTER (
            'Dim_Date',
            [Date] = d
                && [isHoliday]
                && ( WEEKDAY ( [Date] ) IN { 1, 7 } )
        )
    ) = 0
VAR beforeWorkTime =
    t < TIME ( 9, 0, 0 )
VAR inWorkTime =
    AND ( t >= TIME ( 9, 0, 0 ), t <= TIME ( 17, 0, 0 ) )
RETURN
    SWITCH (
        TRUE (),
        isWorkDay && inWorkTime, d + t + 4 / 24,
        isWorkDay && BeforeWorkTime, d + TIME ( 9, 0, 0 ) + 4 / 24,
        CALCULATE (
            MIN ( 'Dim_Date'[Date] ),
            FILTER (
                'Dim_Date',
                [Date] > d
                    && NOT ( [isHoliday] )
                    && NOT ( WEEKDAY ( [Date] ) IN { 1, 7 } )
            )
        )
            + TIME ( 9, 0, 0 ) + 4 / 24
    )

If it doesn't meet your requirement, Could you please provide a mockup sample based on fake data or describle the fields of each tables and the relations between tables simply? It will be helpful if you can show us the exact expected result based on the tables. Please upload your files to One Drive and share the link here.

Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

javirmerino · ‎01-27-2020

Thanks for the timely response, @v-lid-msft! That's almost sorted it, but the hours added needs to be dynamic based on the urgency level. I'm also having some issues with the [date] and [time] fields in your query showing as incorrect syntax. Could it be that i have a calculated field within my extract with these names also?

I've amended your query very slightly to represent the actual field names in my data source as below;

Measure = VAR d = [Date]
VAR t = [Time]
VAR isWorkDay =
    CALCULATE (
        COUNTROWS ( 'Dim_Date' ),
        FILTER (
            'Dim_Date',
            [Date] = d
                && [IsHolidayUK]
                && ( WEEKDAY ( [Date] ) IN { 1, 7 } )
        )
    ) = 0
VAR beforeWorkTime =
    t < TIME ( 9, 0, 0 )
VAR inWorkTime =
    AND ( t >= TIME ( 9, 0, 0 ), t <= TIME ( 17, 0, 0 ) )
RETURN
    SWITCH (
        TRUE (),
        isWorkDay && inWorkTime, d + t + TblKPI.[hours] / 24,
        isWorkDay && BeforeWorkTime, d + TIME ( 9, 0, 0 ) + 4 / 24,
        CALCULATE (
            MIN ( 'Dim_Date'[Date] ),
            FILTER (
                'Dim_Date',
                [Date] > d
                    && NOT ( [IsHolidayUK] )
                    && NOT ( WEEKDAY ( [Date] ) IN { 1, 7 } )
            )
        )
            + TIME ( 9, 0, 0 ) + 4 / 24
    )

i've also included an example extract of how i expect the data to be produced. See below;

ID

Type

Sub Type

Owner

Title

Description

Status

Due Now Status

Urgency

Received

Required Completion Date

WorkingDayRecieved

DueDate

73971

Maintenance

Other

Operations Support

Ticket Request Title

This contains string data around the query request

New

Missed

Urgent

24/01/2020 14:05

27/01/2020 10:05

73992

Maintenance

Other

Operations Support

Ticket Request Title

This contains string data around the query request

New

Due Now

Urgent

27/01/2020 08:57

27/01/2020 09:00

27/01/2020 13:00

73993

Maintenance

Other

Operations Support

Ticket Request Title

This contains string data around the query request

New

Due Now

Urgent

27/01/2020 08:59

27/01/2020 09:00

27/01/2020 13:00

73987

Maintenance

Other

Operations Support

Ticket Request Title

This contains string data around the query request

New

Due Now

Very High

24/01/2020 17:47

27/01/2020 09:00

27/01/2020 17:00

73940

Maintenance

Other

Operations Support

Ticket Request Title

This contains string data around the query request

New

Due Now

High

24/01/2020 09:56

28/01/2020 09:56

73948

Maintenance

Other

Operations Support

Ticket Request Title

This contains string data around the query request

New

Due Now

High

24/01/2020 11:18

28/01/2020 11:18

73927

Maintenance

Other

Operations Support

Ticket Request Title

This contains string data around the query request

New

Due Now

Normal

24/01/2020 07:58

24/01/2020 09:00

29/01/2020 09:00

73873

Maintenance

Other

Operations Support

Ticket Request Title

This contains string data around the query request

New

Due Now

Low

22/01/2020 16:35

29/01/2020 16:35

73968

Maintenance

Other

Operations Support

Ticket Request Title

This contains string data around the query request

New

Due Now

Low

24/01/2020 13:45

27/01/2020 17:00

24/01/2020 13:45

27/01/2020 17:00

Received is the date/time of the received request, straight form the source with no manipulation.

WorkingDayRecieved is calculated based on the Received field, so it defaults to the start of the next working day (9am if raised before 9am, excludes weekends and bank holidays etc)

Required Completion Date when this field is populated with a date/time, this value should be the DueDate of the record. When empty, the due date should be calculated based on the WorkingDayRecieved field plus hours dtermined by the urgency of the request. As you'll see, the first 3 Urgent records have a 4 hour window for completion. The 4th record (ID 73987) is Very High so has been allocated an 8-hour completion window. Meanwhile ID 73873 is low priority so have 40 working hours to complete.

I hope that clarifies any ambiguities, but i'm here to answer anything else that needs clarity. Thanks again for responding so quickly.

A

bwalton · ‎01-25-2020

Hi, I am not sure I have an answer to your question but was curious how you connected sysaid to Power Bi ?

Any insight would be great .

Thanks you ,

javirmerino · ‎01-28-2020

Hi @bwalton, thanks for your comment.

I've connected using a sql server connection into our applications server where the SysAid data is held.

Do you currently use SysAid, then? What's your opinions?

A

v-lid-msft · ‎02-11-2020

Hi @javirmerino ,

Sorry for delay in response, We can try to use the following calculated column to meet your requirement:

Required Completion Date =
IF (
    [Required Completion Date] = BLANK ()
        || [Required Completion Date] = "",
    VAR d = [Date]
    VAR t = [Time]
    VAR HourValue =
        SWITCH ( [Urgency], "Urgent", 4, "Very High", 8, "Normal", 24, "Low", 40 )
    VAR isWorkDay =
        CALCULATE (
            COUNTROWS ( 'Dim_Date' ),
            FILTER (
                'Dim_Date',
                [Date] = d
                    && [IsHolidayUK]
                    && ( WEEKDAY ( [Date] ) IN { 1, 7 } )
            )
        ) = 0
    VAR beforeWorkTime =
        t < TIME ( 9, 0, 0 )
    VAR inWorkTime =
        AND ( t >= TIME ( 9, 0, 0 ), t <= TIME ( 17, 0, 0 ) )
    RETURN
        SWITCH (
            TRUE (),
            isWorkDay && inWorkTime, d + t + HourValue / 24,
            isWorkDay && BeforeWorkTime, d + TIME ( 9, 0, 0 ) + HourValue / 24,
            CALCULATE (
                MIN ( 'Dim_Date'[Date] ),
                FILTER (
                    'Dim_Date',
                    [Date] > d
                        && NOT ( [IsHolidayUK] )
                        && NOT ( WEEKDAY ( [Date] ) IN { 1, 7 } )
                )
            )
                + TIME ( 9, 0, 0 ) + HourValue / 24
        ),
    [Required Completion Date]
)

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Calculating Required Completion Time/Date based on predetermined SLAs

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly