Forum Discussion

Sykboardrider's avatar
Sykboardrider
Frequent Visitor
5 years ago
Solved

Calculate difference between two dates excluding weekends and show positive or negative days.

Hi.

 

I am trying to create a calculated column to work order that can show the number of days between two dates, this must exclude weekends and be able to show the days as either + or -

 

Currently, the DAX expression below is giving me the correct + answer but no negative answers:

 

Initial Response Difference = CALCULATE(SUM('Calendar'[IsWeekday]),DATESBETWEEN('Calendar'[Date],'Work Order'[SVMXC__Initial_Response_Customer_By__c],'Work Order'[SVMXC__Actual_Initial_Response__c]))
 
If I try the following DAX expression, it give bot + & - answers, but is including weekends
 
Initial Resp Diff = var diff = DATEDIFF('Work Order'[SVMXC__Initial_Response_Customer_By__c],'Work Order'[SVMXC__Actual_Initial_Response__c],DAY)
var diff2 = DATEDIFF('Work Order'[SVMXC__Actual_Initial_Response__c],'Work Order'[SVMXC__Initial_Response_Customer_By__c],DAY)
return
IF('Work Order'[SVMXC__Initial_Response_Customer_By__c]<='Work Order'[SVMXC__Actual_Initial_Response__c],diff,0-diff2)
 
My goal is to calculate the number of days + or - between SVMXC__Initial_Response_Customer_By__c & SVMXC__Actual_Initial_Response__c, because SVMXC__Actual_Initial_Response__c can be before or after SVMXC__Initial_Response_Customer_By__c
 
Many thanks.
 
Need Help
  • Sykboardrider's avatar
    Sykboardrider
    5 years ago

    Thanks,

     

    I have already got a weekday calculation in place on my calendar. I am not sure how I can use this to work out the number of weekdays + or - between two dates.

     

    My goal is to calculate the number of days + or - between SVMXC__Initial_Response_Customer_By__c & SVMXC__Actual_Initial_Response__c, because SVMXC__Actual_Initial_Response__c can be before or after SVMXC__Initial_Response_Customer_By__c

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Sykboardrider , example of workday calculations

    new column

    Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

     

     

    new measure

    Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Min(Table[Start Date]),Max(Table[End Date])),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

     

     

    • Sykboardrider's avatar
      Sykboardrider
      Frequent Visitor
      5 years ago

      Thanks,

       

      I have already got a weekday calculation in place on my calendar. I am not sure how I can use this to work out the number of weekdays + or - between two dates.

       

      My goal is to calculate the number of days + or - between SVMXC__Initial_Response_Customer_By__c & SVMXC__Actual_Initial_Response__c, because SVMXC__Actual_Initial_Response__c can be before or after SVMXC__Initial_Response_Customer_By__c