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

shado26's avatar
shado26
Helper III
8 years ago

Hi v-jiascu-msft

 

i want to thank you to help me on this its work fine now after instal the new version of Power PI 

 

i have one question to ask 

 

can we add on this to calculate the SLA from working hours

 

like Ex 

 

if i received order on 5:30 PM  

and approved next working day on 9:30 

 

i calculation of SLA should be 30 min in evening and 30 min in morning to this will be met the SLA

 

another Ex 

if i received order on 5:30 PM  

but approved next working day on 10:30 

this SLA for this will be 2 h for this should count as faild 

 

many thanks 

shadi 

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

Hi shado26,

 

I have a similar case today. You can add a new column to computer the work hours only. Please check the demo here.

AllInOne =
VAR standardStart =
    IF (
        TIMEVALUE ( [Start Time] ) < TIME ( 9, 0, 0 ),
        DATEVALUE ( [Start Time] ) + TIME ( 9, 0, 0 ),
        IF (
            TIMEVALUE ( [Start Time] ) > TIME ( 17, 0, 0 ),
            DATEVALUE ( [Start Time] ) + TIME ( 17, 0, 0 ),
            [Start Time]
        )
    )
VAR standardEnd =
    IF (
        TIMEVALUE ( [End Time] ) < TIME ( 9, 0, 0 ),
        DATEVALUE ( [End Time] ) + TIME ( 9, 0, 0 ),
        IF (
            TIMEVALUE ( [End Time] ) > TIME ( 17, 0, 0 ),
            DATEVALUE ( [End Time] ) + TIME ( 17, 0, 0 ),
            [End Time]
        )
    )
VAR businessDay =
    CALCULATE (
        SUMX ( DateTable, [IsWorkday] ),
        FILTER (
            'DateTable',
            'DateTable'[Date] > [Start Time].[Date]
                && 'DateTable'[Date] < [End Time].[Date]
        )
    )
RETURN
    IF (
        [Start Time] > [End Time],
        9999,
        IF (
            [Start Time].[Date] = [End Time].[Date],
            DATEDIFF ( standardstart, standardend, HOUR ),
            [Business Days] * 8
                + DATEDIFF ( TIMEVALUE ( standardstart ), TIME ( 17, 0, 0 ), HOUR )
                + DATEDIFF ( TIME ( 9, 0, 0 ), TIMEVALUE ( standardend ), HOUR )
        )
    )

Computing_Time_Metric_Difference_of_Two_Time_Values

Best Regards,

Dale

  • shado26's avatar
    shado26
    Helper III
    8 years ago

    HI v-jiascu-msft

     

    thank for this tips 

     

    but what im lookiing for our noraml standard work time from 9 AM to 5 PM 

     

    we have 1 month during the year we start at 9 AM and End on 3 PM 

     

    how we can inculde this time in this month on main forumla 

     

    or can we create table for Workday and include working time  ?? 

     

    to make more easy lets us say on Jan our working time from 9 AM to 5 PM 

     

    and Feb will be from 9 AM to 3 PM