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

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's avatar
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'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

    • Anonymous's avatar
      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
        )
    )
      • ssvr's avatar
        ssvr
        Helper III
        8 years ago

        Anonymous

         

        some error in DAX

         

    • 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.