Forum Discussion

ssvr's avatar
ssvr
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...
Need Help
ssvr's avatar
ssvr
Helper III
8 years ago

Phil_Seamark

 

Could you please resolve my issue.

 

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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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.