Forum Discussion

shado26's avatar
shado26
Icon for Helper III rankHelper 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 these dates/ time 

moreover i have standred time 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 

 

below are screen shot which i used to make on Excel but im planning now to make this report live to power BI 

 

https://imgur.com/a/FVAMt

 

 

  • 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

21 Replies

  • shado26's avatar
    shado26
    Icon for Helper III rankHelper III
    8 years ago

    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 these dates/ time 

    moreover i have standred time 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 

     

    below are screen shot which i used to make on Excel but im planning now to make this report live to power BI 

     

    https://imgur.com/a/FVAMt

     

     

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Icon for Microsoft Employee rankMicrosoft 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
        Icon for Helper III rankHelper 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