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

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

Accepted Solutions
Highlighted
Super User III
Super User III

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


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

10 REPLIES 10
Highlighted
Super User III
Super User III

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


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

Highlighted
Frequent Visitor

Re: Datediff with filters to exclude weekends

@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

 

 

 

Highlighted
Frequent Visitor

Re: Datediff with filters to exclude weekends

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

Highlighted
Frequent Visitor

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

Highlighted
Super User III
Super User III

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.

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





Highlighted
Frequent Visitor

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 III
Super User III

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


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





Highlighted
Frequent Visitor

Re: Datediff with filters to exclude weekends

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?

Highlighted
Super User III
Super User III

Re: Datediff with filters to exclude weekends

You need to adjuat the column name to your models.

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





Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors