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