topic Re: dateDiff in weekdays function in DAX Commands and Tips

dateDiff in weekdays function

Anonymous — Tue, 07 Jun 2022 07:30:36 GMT

Hi there,

i've been using the following function to calculate the date difference in weekdays between 2 columns (creationDate, closureDate);

= (InitialDate as date, FinalDate as date ) as number =>
let
DaysBetweenDates = Duration.Days(FinalDate-InitialDate),
DaysList = List.Dates(List.Min({InitialDate,FinalDate}),Number.Abs(DaysBetweenDates)+1, Duration.From(1)),
WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ),
WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList)
in
WorkingDays

The function lets me select 2 columns, but since i'm trying to calculate processing time and i'm only using creation- and closureDate, i can only calculate the processing time of tickets already closed. I'm trying to get a third column into the mix that takes the currentDate if closureDate is empty, but i can't seem to get that IF statement to work at all 😕 anyone knows the solution?

Thanks in advance,

Re: dateDiff in weekdays function

ChrisClever — Tue, 07 Jun 2022 10:04:27 GMT

This is a PQ question Anonymous 🙂 But take a look at this:

= (InitialDate as date, FinalDate as date) as number => let FinalDateCheck = Number.From(each if [Final Date] = null then Date.From(DateTime.LocalNow()) else [Final Date]), DaysBetweenDates = Duration.Days(FinalDateCheck-InitialDate), DaysList = List.Dates(List.Min({InitialDate,FinalDateCheck}),Number.Abs(DaysBetweenDates)+1, Duration.From(1)), WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ), WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList) in WorkingDays

I modified your function with a simple if statement.

Re: dateDiff in weekdays function

Anonymous — Tue, 07 Jun 2022 10:19:28 GMT

Hi there ChrisClever ,

I just noticed that i had selected the wrong forum; oops :0

I've tried the adjustment to the function, but it gives the following error:

DataFormat.Error: can't convert to Number.
Details:
[Function]

I'm guessing there is something wrong with the data types somewhere, so i changed the intialdate and finaldate to datetime in the first line, but it hasn't fixed the error.

i'm not quite sure what exaclty can't be converted to number 😕

Re: dateDiff in weekdays function

ChrisClever — Tue, 07 Jun 2022 10:15:01 GMT

Did you copy and paste the entire code?

My guess is you've just added the FinalDateCheck line without modifying the rest of the code 🙂

Take a close look - you need to change FinalDate to FinalDateCheck in your calculations.

Re: dateDiff in weekdays function

Anonymous — Tue, 07 Jun 2022 11:39:50 GMT

yup, i've copied all of the code, and tried all of the data types.

The function should give dateDiff in weekdays as whole numbers.

Still unclear what causes the conversion to number error.

I tried changing line 3;

FinalDateCheck = Number.From(each.....

to:

FinalDateCheck = Date.from(each....

and now it gives the same DataFormat.Error except for number it now says date:

can't convert to date

lastly i've removed the spaces between Final Date in line 3, sadly without effect.

Re: dateDiff in weekdays function

ChrisClever — Tue, 07 Jun 2022 13:01:52 GMT

Ok,

So the problem here is the null value in the FinalDate Column. The easy fix would be to replace the nulls with the current date before adding the custom function column. And then your code should be working just fine.

#"Replace Value" = Table.ReplaceValue(#"Changed type",null,Date.From(DateTime.LocalNow()),Replacer.ReplaceValue,{"Final Date"})

Re: dateDiff in weekdays function

Anonymous — Tue, 07 Jun 2022 14:20:08 GMT

Thanks so much!

The original function works fine now that all the null values out of closureDate have been replaced by DateTime.LocalNow

Thanks again ^^