Need help after calculating date difference excluding weekends.

[BW617]

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!

[ThaddeusB]

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)?  

[BW617]

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!

[Greg_Deckler · Accepted solution]

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)