Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PowerBInewb
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
MFelix
Super User
Super User

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

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



Anonymous
Not applicable

Can this be represented in hours?

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

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



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.

 

 

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



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?

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



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

@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

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.