Datediff with filters to exclude weekends

[PowerBInewb]

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 · Accepted solution]

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

[Anonymous]

Can this be represented in hours?

[PowerBInewb]

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]

Convert the new column to a whole number type.
I will check how you can convert the column to a measure in direct query.

[PowerBInewb]

That did the trick. Converting that column to a whole number! Thank you so much!

I did have to remove the +1 at the end of the formula. I want 0 days to = a same day repair in my pie charts, and that +1 I think will mess that up.

Awesome! Now I just need to fill in the holidays and preferably keep it Direct Query.

[MFelix]

Hi @PowerBInewb,

Try the following two measure for the direct query:

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




Working days total =
IF (
    HASONEFILTER ( Tickets[Ticket] );
    [WorkingDays measure];
    SUMX ( ALLSELECTED ( Tickets[Ticket] ); [WorkingDays measure] )
)

The first measure is the calculation however when you place it on a table it will give a strange total, if you want to have the sum of the working days on projects use the second measure on the table visual.

Should work as intended, be aware that this calculation is based on the max and values of date so based on row level context.

Regards,

MFelix

Hi @PowerBInewb,

Try the following two measure for the direct query:

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




Working days total =
IF (
    HASONEFILTER ( Tickets[Ticket] );
    [WorkingDays measure];
    SUMX ( ALLSELECTED ( Tickets[Ticket] ); [WorkingDays measure] )
)

The first measure is the calculation however when you place it on a table it will give a strange total, if you want to have the sum of the working days on projects use the second measure on the table visual.

Should work as intended, be aware that this calculation is based on the max and values of date so based on row level context.

Regards,

MFelix

[PowerBInewb]

Roger that, measures are inputted.

The first measure seems to work, but after I insert the second one into the table, PowerBI just spins its wheel (literally). 5 minutes without any addition to the table to show the result. Eventually this error comes up:

"The operation was cancelled bcause of locking conflicts."

An additional question:

Working days total =
IF (
    HASONEFILTER ( Tickets[Ticket] );
    [WorkingDays measure];
    SUMX ( ALLSELECTED ( Tickets[Ticket] ); [WorkingDays measure] )
)

By tickets are you refering to my "callnumber" column?

[MFelix]

You need to adjuat the column name to your models.

[PowerBInewb]

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]

@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.