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
LyonsBI_BRL
Helper III
Helper III

Cannot convert value #datetime to Date

I'm attempting to calculate the number of working days for each Employee, however I'm running into an issue when attempting to calculate it. 


Using the supplied function below, I use this to calculate the number of 

 

(StartDate as date, EndDate as date) as number =>

let

    DateList = List.Dates(StartDate, Number.From(EndDate - StartDate), #duration(1, 0, 0, 0)),
    RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
    RemoveHolidays = List.RemoveItems(RemoveWeekends),
    CountDays = List.Count(RemoveHolidays)
in
    CountDays

 

 

I then invoke a Custom Function selecting my BeginDate column and EndDate column

LyonsBI_BRL_0-1633863016462.png

 

 However it's giving me all Errors in the NWD column and saying this error message below. What am I doing wrong here, there's something I'm missing that's causing this.

Expression.Error: We cannot convert the value #datetime(2013, 5, 7, 22, 0, 0) to type Date.
Details:
Value=5/7/2013 10:00:00 PM
Type=[Type]

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@LyonsBI_BRL 
 The Function NWD asks for Date, not Date/Time value. Before invoking the function, Under Transform Tab, Date > Click on Date Only to convert to Date

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

2 REPLIES 2
Fowmy
Super User
Super User

@LyonsBI_BRL 
 The Function NWD asks for Date, not Date/Time value. Before invoking the function, Under Transform Tab, Date > Click on Date Only to convert to Date

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

That did it thank you!

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.