Forum Discussion

javirmerino's avatar
javirmerino
Helper III
6 years ago

Calculating Required Completion Time/Date based on predetermined SLAs

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 ...
Need Help
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    javirmerino
    Helper III
    6 years ago

    Hi Anonymous, 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's avatar
      v-lid-msft
      Community Support
      6 years ago

      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,