Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Thanatos001
Frequent Visitor

dateDiff in weekdays function

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,

1 ACCEPTED SOLUTION

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"})

 

ChrisClever_0-1654606728731.png

 

View solution in original post

6 REPLIES 6
Thanatos001
Frequent Visitor

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 😕

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"})

 

ChrisClever_0-1654606728731.png

 

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 ^^

 

 

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.

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.

ChrisClever
Frequent Visitor

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

ChrisClever_0-1654591633622.png

 

 

= (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.

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors