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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.