Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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)
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!
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
101 | |
78 | |
70 | |
63 |
User | Count |
---|---|
141 | |
106 | |
101 | |
85 | |
72 |