cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LukeWatsonAJG
Frequent Visitor

Time to resolution excluding Weekends

Hi, 

 

I've read through various topics surround this however I haven't been able to reproduce or get this working.

 

I need to calculate the date difference between two dates excluding a weekend.

 

I have two tables with a relation ship between Date & Resolved Date.

How do i calculate difference between Last Resolved Date & Submit date, excluding weekends.

 

DimDate 

1.PNG

 

ResolvedINC2.PNG

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @LukeWatsonAJG ,

 

If you want to count work days, create a measure like so:

Measure =
CALCULATE (
    COUNT ( DimDate[Date] ) + 0,
    FILTER (
        DimDate,
        [Is Weekend] = 0
            && [Date] >= DATEVALUE ( MAX ( ResolvedINC[Submit date] ) )
            && [Date] <= DATEVALUE ( MAX ( ResolvedINC[Resolved Date] ) )
    )
)

 

If you want to calculate the specific time, try to create a measure like so:

Measure 2 = 
VAR MinWeekend =
    CALCULATE (
        MIN ( DimDate[Date] ),
        FILTER (
            DimDate,
            DimDate[Is Weekend] = 1
                && DimDate[Date] >= DATEVALUE ( MAX ( ResolvedINC[Submit date] ) )
                && DimDate[Date] <= DATEVALUE ( MAX ( ResolvedINC[Resolved Date] ) )
        )
    )
VAR MaxWeekend =
    CALCULATE (
        MAX ( DimDate[Date] ),
        FILTER (
            DimDate,
            DimDate[Is Weekend] = 1
                && DimDate[Date] >= DATEVALUE ( MAX ( ResolvedINC[Submit date] ) )
                && DimDate[Date] <= DATEVALUE ( MAX ( ResolvedINC[Resolved Date] ) )
        )
    )
VAR MaxWeekend_ =
    IF ( MaxWeekend <> BLANK (), MaxWeekend + 1 )
VAR DateDiff1 =
    DATEDIFF (
        MAX ( ResolvedINC[Submit date] ),
        MAX ( ResolvedINC[Resolved Date] ),
        SECOND
    )
VAR DateDiff2 =
    DATEDIFF ( MAX ( ResolvedINC[Submit date] ), MinWeekend, SECOND )
        + DATEDIFF ( MaxWeekend_, MAX ( ResolvedINC[Resolved Date] ), SECOND )
VAR DateDiff3 =
    DATEDIFF ( MaxWeekend_, MAX ( ResolvedINC[Resolved Date] ), SECOND )
VAR DateDiff_ =
    IF (
        MinWeekend = BLANK (),
        DateDiff1,
        IF (
            MAX ( ResolvedINC[Submit date] ) < MinWeekend
                && MAX ( ResolvedINC[Resolved Date] ) > MaxWeekend_,
            DateDiff2,
            IF (
                MAX ( ResolvedINC[Submit date] ) >= MinWeekend
                    && MAX ( ResolvedINC[Submit date] ) <= MaxWeekend_,
                DateDiff3
            )
        )
    )
VAR Days =
    TRUNC ( DateDiff_ / 24 / 3600 )
VAR Hours =
    TRUNC ( ( DateDiff_ - Days * 24 * 3600 ) / 3600 )
VAR Minutes =
    TRUNC ( ( DateDiff_ - Hours * 3600 - Days * 24 * 3600 ) / 60 )
VAR Seconds = DateDiff_ - Minutes * 60 - Hours * 3600 - Days * 24 * 3600
VAR Result =
    Days & ":"
        & FORMAT ( Hours, "00" ) & ":"
        & FORMAT ( Minutes, "00" ) & ":"
        & FORMAT ( Seconds, "00" )
RETURN
    Result

datediff.PNG

 

BTW, .pbix file attached.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Icey
Community Support
Community Support

Hi @LukeWatsonAJG ,

 

If you want to count work days, create a measure like so:

Measure =
CALCULATE (
    COUNT ( DimDate[Date] ) + 0,
    FILTER (
        DimDate,
        [Is Weekend] = 0
            && [Date] >= DATEVALUE ( MAX ( ResolvedINC[Submit date] ) )
            && [Date] <= DATEVALUE ( MAX ( ResolvedINC[Resolved Date] ) )
    )
)

 

If you want to calculate the specific time, try to create a measure like so:

Measure 2 = 
VAR MinWeekend =
    CALCULATE (
        MIN ( DimDate[Date] ),
        FILTER (
            DimDate,
            DimDate[Is Weekend] = 1
                && DimDate[Date] >= DATEVALUE ( MAX ( ResolvedINC[Submit date] ) )
                && DimDate[Date] <= DATEVALUE ( MAX ( ResolvedINC[Resolved Date] ) )
        )
    )
VAR MaxWeekend =
    CALCULATE (
        MAX ( DimDate[Date] ),
        FILTER (
            DimDate,
            DimDate[Is Weekend] = 1
                && DimDate[Date] >= DATEVALUE ( MAX ( ResolvedINC[Submit date] ) )
                && DimDate[Date] <= DATEVALUE ( MAX ( ResolvedINC[Resolved Date] ) )
        )
    )
VAR MaxWeekend_ =
    IF ( MaxWeekend <> BLANK (), MaxWeekend + 1 )
VAR DateDiff1 =
    DATEDIFF (
        MAX ( ResolvedINC[Submit date] ),
        MAX ( ResolvedINC[Resolved Date] ),
        SECOND
    )
VAR DateDiff2 =
    DATEDIFF ( MAX ( ResolvedINC[Submit date] ), MinWeekend, SECOND )
        + DATEDIFF ( MaxWeekend_, MAX ( ResolvedINC[Resolved Date] ), SECOND )
VAR DateDiff3 =
    DATEDIFF ( MaxWeekend_, MAX ( ResolvedINC[Resolved Date] ), SECOND )
VAR DateDiff_ =
    IF (
        MinWeekend = BLANK (),
        DateDiff1,
        IF (
            MAX ( ResolvedINC[Submit date] ) < MinWeekend
                && MAX ( ResolvedINC[Resolved Date] ) > MaxWeekend_,
            DateDiff2,
            IF (
                MAX ( ResolvedINC[Submit date] ) >= MinWeekend
                    && MAX ( ResolvedINC[Submit date] ) <= MaxWeekend_,
                DateDiff3
            )
        )
    )
VAR Days =
    TRUNC ( DateDiff_ / 24 / 3600 )
VAR Hours =
    TRUNC ( ( DateDiff_ - Days * 24 * 3600 ) / 3600 )
VAR Minutes =
    TRUNC ( ( DateDiff_ - Hours * 3600 - Days * 24 * 3600 ) / 60 )
VAR Seconds = DateDiff_ - Minutes * 60 - Hours * 3600 - Days * 24 * 3600
VAR Result =
    Days & ":"
        & FORMAT ( Hours, "00" ) & ":"
        & FORMAT ( Minutes, "00" ) & ":"
        & FORMAT ( Seconds, "00" )
RETURN
    Result

datediff.PNG

 

BTW, .pbix file attached.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

amitchandak
Super User IV
Super User IV

@LukeWatsonAJG , Refer to this file. It has date diff between two dates across two tables. Check For Page 2/Workday diff

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@Icey @amitchandak 

I am struggling with the same issue and i did try mentioned measures and other DAX but no luck here i am sharing with you a sample of the report it would be a great help if you can help

 

https://1drv.ms/u/s!Aj7YLBYfrzPkil3VcmfrgIPNio5c?e=Lat0Jo

 

COuntdays = COUNTROWS(FILTER(ALL(Datetable),Datetable[Date]>= SELECTEDVALUE(Table1[Created On]) && Datetable[Date] <=SELECTEDVALUE(Table1[Solved Date]) && NOT(Datetable[WeekDayNum] in {6,7}) && Datetable[Isworkingday] = 1()))
 
Measure = VAR _st = MAX(Table1[Created On]) VAR _end= SWITCH(MAX( Table1[Status]),"resolved" , MAX(Table1[Resolved Date]) , "active", TODAY(), _st) REturn COUNTROWS(FILTER(Datetable,_st <= Datetable[Date] &&Datetable[Date] <= _end && WEEKDAY(Datetable[Date],2) <6))

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors