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 taking into consideration the weekends ReleaseDt ClosedDt DaysDiff(Excludeweekends) How can I achieve this?Thank you,

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

As a measure (recommended, you'd have to make a strong case to make this a calculated column): [Date Difference Measure] = VAR CreateDate = MIN ( TableName[CreatedDate] ) VAR CloseDate = MIN ( TableName[ClosedDate] ) VAR CalendarNoWeekends = FILTER ( CalendarTable, CalendarTable[Date] >= CreateDate && CalendarTable[Date] <= CloseDate && CalendarTable[Is Working Day] = "Yes" ) RETURN COUNTROWS ( CalendarNoWeekends )As a calculated column (again, make sure you need to add a non-compressed column to your data model before going this way): Date Difference Calculated Column = VAR CreateDate = TableName[CreatedDate] VAR CloseDate = TableName[ClosedDate] VAR CalendarNoWeekends = FILTER ( CalendarTable, CalendarTable[Date] >= CreateDate && CalendarTable[Date] <= CloseDate && CalendarTable[Is Working Day] = "Yes" ) RETURN COUNTROWS ( CalendarNoWeekends )Only difference is you already have a row context, so you don't need to wrap the start and end dates in a MIN() function. Hope this helps.

Hi there. These solved threads might help you: https://community.powerbi.com/t5/Desktop/Calculate-working-days-between-2-dates/td-p/230349 https://community.powerbi.com/t5/Desktop/DATEDIFF-Working-Days/td-p/130662 Regards.

Thanks. But here they suggested create a Date Table for Isweekday flag. I worked on almost 10 years data so, cant possible to create Date Table with Isweekday flag ??

Hi ssvr, Yes. It is supported to create a calendar table with more than 10 years. Regards, Yuliana Gu

Date Difference excluding weekends | Microsoft Fabric Community

16 Replies

MarcoRotta
Resolver I
8 years ago
Hi there.
These solved threads might help you:

https://community.powerbi.com/t5/Desktop/Calculate-working-days-between-2-dates/td-p/230349

https://community.powerbi.com/t5/Desktop/DATEDIFF-Working-Days/td-p/130662

Regards.
- ssvr
  Helper III
  8 years ago
  Thanks.
  
  But here they suggested create a Date Table for Isweekday flag.
  
  I worked on almost 10 years data so, cant possible to create Date Table with Isweekday flag ??
  - v-yulgu-msft
    Microsoft Employee
    8 years ago
    Hi ssvr,
    
    Yes. It is supported to create a calendar table with more than 10 years.
    
    Regards,
    
    Yuliana Gu
Anonymous
Not applicable
6 years ago
Hi,

This maybe the solution:

https://www.youtube.com/watch?v=bs3yzmf9elA https://www.youtube.com/watch?v=bs3yzmf9elA

Regards,

Forum Discussion

Date Difference excluding weekends

16 Replies

Related content

DATEDIFF excluding weekends

Previous Day Balance - Excluding Weekends

Date / Time difference excluding weekends and factoring working hours

calculations excluding weekends

Exclude weekends from Date Measure