cancel
Showing results 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

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
Super User

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

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

Super User

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

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors