DATEDIFF() not giving the right results

Anonymous — Thu, 07 Nov 2024 14:09:27 GMT

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

Re: DATEDIFF() not giving the right results

johnt75 — Thu, 07 Nov 2024 13:45:45 GMT

Try using TODAY() for DateActuelle

Re: DATEDIFF() not giving the right results

Anonymous — Thu, 07 Nov 2024 14:07:57 GMT

I did just that and it works now. Thank you!

topic DATEDIFF() not giving the right results in DAX Commands and Tips

DATEDIFF() not giving the right results

Re: DATEDIFF() not giving the right results

Re: DATEDIFF() not giving the right results