Reply
Frequent Visitor
Posts: 11
Registered: ‎02-09-2017
Accepted Solution

Datediff with filters to exclude weekends

[ Edited ]

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!!!
 
  Inkeddays to repair_LI.jpg
Dim Table.PNGOverall view.PNG

Accepted Solutions
Super User
Posts: 2,234
Registered: ‎09-19-2016

Re: Datediff with filters to exclude weekends

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



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

Proud to be a Datanaut!




View solution in original post


All Replies
Super User
Posts: 2,234
Registered: ‎09-19-2016

Re: Datediff with filters to exclude weekends

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



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

Proud to be a Datanaut!




Frequent Visitor
Posts: 11
Registered: ‎02-09-2017

Re: Datediff with filters to exclude weekends

[ Edited ]

@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.Results from MFelix 1.PNG

 

 

 

Frequent Visitor
Posts: 11
Registered: ‎02-09-2017

Re: Datediff with filters to exclude weekends

[ Edited ]

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.

 

Direct Query.PNG

Frequent Visitor
Posts: 11
Registered: ‎02-09-2017

Re: Datediff with filters to exclude weekends

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.

 

DQ Capture.PNG

Super User
Posts: 2,234
Registered: ‎09-19-2016

Re: Datediff with filters to exclude weekends

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


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

Proud to be a Datanaut!




Frequent Visitor
Posts: 11
Registered: ‎02-09-2017

Re: Datediff with filters to exclude weekends

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

 

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.

 

 

Highlighted
Super User
Posts: 2,234
Registered: ‎09-19-2016

Re: Datediff with filters to exclude weekends

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



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

Proud to be a Datanaut!




Frequent Visitor
Posts: 11
Registered: ‎02-09-2017

Re: Datediff with filters to exclude weekends

[ Edited ]

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 uplocking.PNG:

 

"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?

Super User
Posts: 2,234
Registered: ‎09-19-2016

Re: Datediff with filters to exclude weekends

You need to adjuat the column name to your models.


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

Proud to be a Datanaut!