difference between two days excluding weekends

[sannadisarath]

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.

[ssvr]

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

[v-shex-msft · Accepted solution]

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

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