Forum Discussion

shado26's avatar
shado26
Helper III
8 years ago
Solved

measure date difference power excluding weekend

Dear all   apprecite your assist and support to found way measure date difference power excluding weekend   please note that i have start date/time & End date/time  or i need to measure SLA for t...
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi shado26,

     

    How to override it? The formula in my last post will show up "Error" in the column.

    ifmet = 
VAR sla2 =
    DATEDIFF ( DUMP_DATA_FROM_PURCHASE[start], DUMP_DATA_FROM_PURCHASE[end], SECOND )
RETURN
    IF (
        WEEKDAY ( DUMP_DATA_FROM_PURCHASE[start],2 ) IN { 5, 6 },
        "Week End approval",
        IF (
            TIMEVALUE ( DUMP_DATA_FROM_PURCHASE[start] ) < TIME ( 9, 0, 0 )
                || TIMEVALUE ( DUMP_DATA_FROM_PURCHASE[end] ) > TIME ( 18, 0, 0 ),
            "done",
            IF ( sla2 < 0,"Error",if(sla2 <= 5400, "Met", "Failed" )
        )
    ))

    Best Regards,

    Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi shado26,

     

    You can download the file from here. Can you share your file please?

    measure_date_difference_power_excluding_weekend

     

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi shado26,

 

What's the expected result? What kind of data do you have? The SLA could be:

SLA =
CONCATENATE (
    CONCATENATE (
        CONCATENATE (
            CONCATENATE (
                CONCATENATE ( HOUR ( [Column1] ), " hours," ),
                MINUTE ( [Column1] )
            ),
            " minutes,"
        ),
        SECOND ( [Column1] )
    ),
    "seconds"
)

measure_date_difference_power_excluding_weekend

 

If you want to measure the values based on date, you can add a column like below.

Column = [Column1].[Date]

Best Regards,

Dale

shado26's avatar
shado26
Helper III
8 years ago

Hi v-jiascu-msft

 

thank for your reply 

 

here screenshot from what i have 

https://imgur.com/a/FVAMt

 

i have start date/time & End date/time  and i need to measure SLA for these dates/ time 

moreover i have standred SLA 1h and 30 min to measure if employee has been Met SLA or not 

 

our weekend are friday and saturday

our working hours  from 8:00 AM till 5:00 PM 

 

 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi shado26,

     

    The formula of SLA could be like this. You can try it in this file.

    SLA =
VAR allseconds =
    DATEDIFF ( [Start], [End], SECOND )
VAR days =
    INT ( allseconds / 24 / 60 / 60 )
VAR hours =
    MOD ( INT ( allseconds / 60 / 60 ), 24 )
VAR minutes =
    MOD ( INT ( allseconds / 60 ), 60 )
VAR seconds =
    MOD ( allseconds, 60 )
RETURN
    days & " days "
        & hours
        & " hours "
        & minutes
        & " minutes "
        & seconds
        & " seconds"
    measure_date_difference_power_excluding_weekend2

    I have to ask what is the expected result? Are the start date and the end date in one day? How many standard SLA? What are they?

     

    Best Regards,

    Dale

    • shado26's avatar
      shado26
      Helper III
      8 years ago

      hellow Dale

       

      thank for your feedback we have only one SLA which 1h 30 min 

       

      moreover i want to exclude  weekend which " friday & stauerday " and exclude working hours which "9:00 AM to 6 PM "

       

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi shado26,

         

        Can you post the full name of SLA? What does it stand for? Maybe the full name will help me understanding this scenario. The related question is how to determine if the time meets SLA?

        Another question: do you mean the end date time minus the start date time and excluding the weekends and non-working hours?

        For example: 

        2018-01-01 is Monday. So 2018-01-01 9:00:00, 2018-01-01 10:00:00, the SLA is 1 hours;

        2018-01-05 is Friday. So the SLA is 0;

         

         

        Best Regards,

        Dale