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

difference between two days excluding weekends

Hi All,

 

I want to get difference between two dates excluding weekends, please let me know what could be the formula.

i am pulling data from sharepoint online.

 

Thanks,
Sarath.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: difference between two days excluding weekends

Hi sannadisarath,

 

According to your description, you want to get a date list between two dates except weekends, right?

 

If as I said, you can follow below steps:

1. Use CALENDAR function to create a datetable with two dates.

 

Dax: DateTable = CALENDAR(DATE(2010,1,1),TODAY())

Capture.PNG

 

2. Write dax formula to get the specify dates without weekends.

 

Dax: Date Except Weekends = CALCULATETABLE(DateTable,FILTER(ALL(DateTable), AND(WEEKDAY([Date],1)<> 1,WEEKDAY([Date],1)<> 7))) (Notice: 1 is Sunday, 7 is Saturday)

 

Capture2.PNG

 

I added a calculate column to show the day of week.

 

Reference:

WEEKDAY Function (DAX)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
2 REPLIES 2
Community Support Team
Community Support Team

Re: difference between two days excluding weekends

Hi sannadisarath,

 

According to your description, you want to get a date list between two dates except weekends, right?

 

If as I said, you can follow below steps:

1. Use CALENDAR function to create a datetable with two dates.

 

Dax: DateTable = CALENDAR(DATE(2010,1,1),TODAY())

Capture.PNG

 

2. Write dax formula to get the specify dates without weekends.

 

Dax: Date Except Weekends = CALCULATETABLE(DateTable,FILTER(ALL(DateTable), AND(WEEKDAY([Date],1)<> 1,WEEKDAY([Date],1)<> 7))) (Notice: 1 is Sunday, 7 is Saturday)

 

Capture2.PNG

 

I added a calculate column to show the day of week.

 

Reference:

WEEKDAY Function (DAX)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
ssvr Member
Member

Re: difference between two days excluding weekends

Hi,

DDiff.JPG

I created a Date table:

 

Dim Table=CALENDAR(DATE(2008,1,1),DATE(2018,12,31))

 

I created a new column with is working day or not

 

is work day = SWITCH(WEEKDAY([Date]),1,0,7,0,1)

 

Now I want to create "DateDifference" Column with Createddate & Closeddate (I want to know day diffarence b/w these two dates excluding weekends)

 

CreatedDate   ClosedDate  DateDiffarence