cancel
Showing results for
Did you mean:
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

## Re: difference between two days excluding weekends

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())

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)

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

## Re: difference between two days excluding weekends

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())

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)

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: | |
Member

## Re: difference between two days excluding weekends

Hi,

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