Solved: Datediff with filters to exclude weekends - Page 2

PowerBInewb · ‎12-29-2018

Okay Guys, I have read about 50 posts on this topic and have tried to get a variety of formulas to work, but have been unable to make it work. I am trying to determine the days between open and close of a service ticket excluding weekends. Essentially days to resolution.

Objective: Ticket opened - Ticket Closed & filter out weekends (we can worry about Holidays later).

I can get this to give me output on days between calls:

Days to Repair = datediff('_custom_eviews Service Times - Dan'[Date],'_custom_eviews Service Times - Dan'[CloseDate],DAY)

I have created a date table and created the following switch:

is work day = SWITCH(WEEKDAY([Date]),1,0,7,0,1)

My Calendar table looks like this:

Dim Table = CALENDAR(DATE(2007,1,1),DATE(2019,12,31)) ||||||| This might be an issue as it is over 10 years|||||

The relationship is linked between date on the calandar and the date on table '_custom_eviews Service Times - Dan'

Screenshots are attached. What is the best formula to acheive what I am trying to accomplish.

Thank you so much in advance!!!

MFelix · ‎12-29-2018

Hi @PowerBInewb,

Try to use the following calculated column:

WorkingDays = 
    Tickets[CloseDate] - Tickets[OpenDate] -
    SUMX(
        FILTER(DimDate;
            DimDate[Date] > Tickets[OpenDate] && 
            DimDate[Date] < Tickets[CloseDate]);
        IF(WEEKDAY(DimDate[Date]) = 1 || WEEKDAY(DimDate[Date]) = 7; 1; 0)
    ) + 1

Regards,

MFelix

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

View solution in original post