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
jslayer111
Frequent Visitor

calculate working days

I am using the below function to determine working days but I need it to show 0 if the start date and complted date is the same.  How can I do this?

 

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

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

Hi @jslayer111 

Change the query so that the last couple of lines are

#WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList),
Result = if InitialDate - FinalDate = 0 then 0 else #WorkingDays
        
in
    Result

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

1 REPLY 1
PhilipTreacy
Super User
Super User

Hi @jslayer111 

Change the query so that the last couple of lines are

#WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList),
Result = if InitialDate - FinalDate = 0 then 0 else #WorkingDays
        
in
    Result

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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.