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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.