Forum Discussion
Need help after calculating date difference excluding weekends.
So I have a calculation comparing number of working days between two dates. I have a date table and the calculation works just fine.
CycleTimexWeekends = CALCULATE(SUM(CalendarTable[WorkingDay]),DATESBETWEEN(CalendarTable[Date],Data[Date_Of_Receipt_Formula],Data[Original_Date_of_Response]))
The problem is since it's a sum of the working day count on the date table, if the date of receipt and date of response are the same it counts as 1, where it should be a 0.
I figured a new calculated column to take that column and -1 would work. and it does in instances where the date of receipt and response are the same
| Date_Of_Receipt_Formula | Original_Date_of_Response | CycleTimexWeekends | CycleTime_Corrected |
| 11/23/2018 | 11/23/2018 | 1 | 0 |
However it will throw off all the other dates:
| Date_Of_Receipt_Formula | Original_Date_of_Response | CycleTimexWeekends | CycleTime_Corrected |
| 11/23/2018 | 11/23/2018 | 1 | 0 |
| 11/22/2018 | 11/26/2018 | 3 | 2 |
| 11/23/2018 | 11/26/2018 | 2 | 1 |
| 11/24/2018 | 11/26/2018 | 1 | 0 |
| 11/26/2018 | 11/26/2018 | 1 | 0 |
| 11/22/2018 | 11/27/2018 | 4 | 3 |
| 11/23/2018 | 11/27/2018 | 3 | 2 |
does anyone know a way where I can account for the same date of receipt and response, to have it be 0 but keep all the other logic?
thank you!
Perhaps:
CycleTimexWeekendsCorrected = VAR __cycleTime = CALCULATE(SUM(CalendarTable[WorkingDay]),DATESBETWEEN(CalendarTable[Date],Data[Date_Of_Receipt_Formula],Data[Original_Date_of_Response])) RETURN IF(Data[Date_Of_Receipt_Formula] = Data[Original_Date_of_Response]),0,__cycleTime)
3 Replies
Perhaps:
CycleTimexWeekendsCorrected = VAR __cycleTime = CALCULATE(SUM(CalendarTable[WorkingDay]),DATESBETWEEN(CalendarTable[Date],Data[Date_Of_Receipt_Formula],Data[Original_Date_of_Response])) RETURN IF(Data[Date_Of_Receipt_Formula] = Data[Original_Date_of_Response]),0,__cycleTime)
You could just use an If statement:
IF ([Date_Of_Receipt_Formula]=[Original_Date_of_Response], 0, [CycleTimexWeekends])
But are you sure that is actually what you want? All of your example span a weekend, but what about weekday-weekday? Do you want 11/26-11/27 to be counted as 2 days (as your forumula will do)?
ahhh, in all of this I forgot about that simple solution, that would work just fine. I'm measuring response to customers, so if the date is the same I would want it to equal 0. thanks for the help!
