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

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

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.

  • PowerBInewb's avatar
    PowerBInewb
    Frequent Visitor
    7 years ago

    Did you ever find an answer to this? I have been struggling for a few days trying to accomplish the same measure.

     

    In my case:

     

    Call Close - Call open then filter out work days from a isweekday column on my date table. I can't seem to get any of these suggestions to work.

     

    Thanks!