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 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

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 

     

     

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