Hi,
I'm having trouble writing a measure.
I have two tables:
- One that tells me when a design process is due (named
"Date Initiale (Demandée com)") and the actual date it has been delivered at (named "Task Completion date").
- The other one, is my Calendar that has two boolean columns that tell me wether a given day is a weekend (named "Jours OFF") or holiday (named "Non travaillé"). With 1 if it is and 0, if not.
I want to figure out how long my design processes have been late (due date is passed but task completion date for the given process is after the due date or is still empty). So the logic I followed is:
- If a design process has been delivered late, I use it's task completion date for the calculation.
- If it's still ongoing (meaninng no task completion exist) I'll use today as the end date for the calculation.
- Then I use DateDiff to identify the difference in days between the due date and the end date. To that result, I remove all weekends and holidays that may be included in that frame to only count working days.
The problem is, the results aren't correct at all and it seems DateDiff might be the reason. Though, I'm not sure why it's the case.
For example here's a table with the results of my measure for all ongoing late process (delay(days)) with their due dates. As they all don't have a completion date, I'm using Today as end date to make my calculations:
| Design process code | Delay(days) | Year | Month | Day |
| XXXXXXXX | 23 | 2024 | Oct | 4 |
| YYYYYYYY | 12 | 2024 | Sept | 20 |
| ZZZZZZZZ | 12 | 2024 | Oct | 18 |
| VVVVVVV | 11 | 2024 | Oct | 7 |
| WWWWW | 3 | 2024 | Oct | 31 |
| TTTTTTTTT | 2 | 2024 | Nov | 4 |
The process T shouldn't be 2 days late but 3 since we're November 7th and the process X shouldn't be only 20 days from September to today as November 1st was the sole holiday during that period.
Here is the measure:
Retard par Notification =
VAR DateInitiale = MAX( 'Tasks details'[Date Initiale (Demandée com)])
VAR TaskCompletionDate = MAX( 'Tasks details'[Task Completion date])
VAR DateActuelle = MAX('_Calendrier'[Date])
VAR DateFin = IF(NOT(ISBLANK(TaskCompletionDate)), TaskCompletionDate, DateActuelle)
-- here is to manage the holidays and weekends
VAR JoursOff =
CALCULATE(
COUNT('_Calendrier'[Date]),
'_Calendrier'[Date] >= DateInitiale &&
'_Calendrier'[Date] <= DateFin &&
('_Calendrier'[Jours OFF] = 1 || '_Calendrier'[Non travaillé] = 1)
)
VAR RetardNet = DATEDIFF(DateInitiale, DateFin, DAY) +1 - JoursOff
RETURN
IF(RetardNet > 0, RetardNet, BLANK())
