Forum Discussion
Date Difference excluding weekends
I have a starting date and an ending date and I need to count only the working days in between,
i have created New Date Table (ftCalendar) then i have tried this new column
WorkingDays =
CALCULATE(
COUNTROWS ( 'ftCalendar'),
DATESBETWEEN ( 'ftCalendar'[Date], Table[RequestClosedDate], Table[RequestReceivedDate] -1 ),
'ftCalendar'[DateIsWorkingDay] = TRUE,
ALL ( Table )
)
i have tried Power Query also
Get Data --> Blan Query then i have used
= (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
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
it's Not working
Thanks in advance,
Rajesh
3 Replies
Hi Mariusz Thanks for your Reply
It's Not working for me, i don't know what is the issue Herewith i have attached my PBIX fileClick Here Could you please check this?
Regards,
Rajesh R
Hi Anonymous ,
Look at these solutions
https://community.powerbi.com/t5/Desktop/Time-to-resolution-excluding-Weekends/m-p/1127330
https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
