Forum Discussion

Helper III

8 years ago

Date Difference excluding weekends

Hello, I have a starting date and an ending date and I need to count only the working days in between. In other words, the date difference (DATEIFF) between the ending and the starting without tak...

Microsoft Employee

8 years ago

Hi ssvr,

Yes. It is supported to create a calendar table with more than 10 years.

Regards,

Yuliana Gu

Helper III

8 years ago

Hi @Yuliana,

Thanks.

But its difficult to add 10 years canlender and we have to add for future dates also right ?

can you suggest any alternative for this requirement

v-yulgu-msft
Microsoft Employee
8 years ago
Hi ssvr,

You can create calendar table with CALENDAR() function like this:

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

Regards,

Yuliana Gu
- ssvr
  Helper III
  8 years ago
  Thanks v-yulgu-msft
  
  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
  
  Please guide me how to get this done
  - Anonymous
    Not applicable
    8 years ago
    Something like this:
    
    [Date Diff Measure] = VAR Created = MAX ( Table[CreatedDate] ) VAR Closed = MAX ( Table[ClosedDate] ) RETURN CALCULATE ( SUM ( 'Dim Table'[is work day] ), FILTER ( ALL ( 'Dim Table'[DateColumn] ), 'Dim Table'[DateColumn] >= Created && 'Dim Table'[DateColumn] <= Closed ) )

Related content