Forum Discussion

Frequent Visitor

7 years ago

Solved

Datediff with filters to exclude weekends

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

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

10 Replies

MFelix
Super User
7 years ago
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
- PowerBInewb
  Frequent Visitor
  7 years ago
  MFelixThank you so much for getting back to me. I went ahead and changed the naming of my dim table to how you laid out in the example for sake of simplicity.
  
  Upon copying it in and putting my appropriate fields in place I am getting a dax error. Please see the attached image.
- PowerBInewb
  Frequent Visitor
  7 years ago
  Okay one more update. I removed all the semi-colons from your original formula. Now I am getting an error about sumx not being allowed in direct query mode. I'd like to keep it direct if possible. I am allowing unrestricted measures in DQ.
- PowerBInewb
  Frequent Visitor
  7 years ago
  I made a new copy of my dashboard and took it out of Direct Query. Sumx worked without issue. The results were interesting. Instead of showing the difference in days excluding the weekend it gave me the following screenshots. Basically a random date. I just want the total number of days that passed from open to close.
  - MFelix
    Super User
    7 years ago
    Convert the new column to a whole number type.
    I will check how you can convert the column to a measure in direct query.