Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Can someone help me how to caluculate Difference between two DateTime columns without public holidays and weekend?
I have startdate and enddate in one table, Holidays dates list in other table.
It would be a great help.
Thank you.
Solved! Go to Solution.
Hi @Anonymous ,
this function should work:
(StartDate as date, EndDate as date, optional Holidays as list) =>
let
ListOfDates = List.Dates(StartDate,Number.From(EndDate-StartDate),#duration(1,0,0,0)),
DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, Holidays),
DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_,1) < 5 ),
CountDays = List.Count(DeleteWeekends)
in
CountDays
reference the column with the holiday dates in its 3rd parameter
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @Anonymous
Please see the attached file with a solution.
Hi, what if an weekend day is actually a working day by some reasons? Is there any method to calculate back that weekend day as working day? Like how to create another list for special working day which is actually in some of weekend day?
Hi @Anonymous
Please see the attached file with a solution.
Thank you for a this solution.
@ImkeF might have a solution for Power Query. I wrote one for DAX. https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109
Thank you for your quick response.
I would like to have it in Power Query, i want to use that column in further caluculations. Is there any function where i can enter my startdate, enddate columns and compare and exclude the weekend and holidays?
I'm not the Power Query guru, which is why I tagged @ImkeF . I will say that you could implement the DAX as a column instead of a measure.
Hi @Anonymous ,
this function should work:
(StartDate as date, EndDate as date, optional Holidays as list) =>
let
ListOfDates = List.Dates(StartDate,Number.From(EndDate-StartDate),#duration(1,0,0,0)),
DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, Holidays),
DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_,1) < 5 ),
CountDays = List.Count(DeleteWeekends)
in
CountDays
reference the column with the holiday dates in its 3rd parameter
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thank you for a quik and easy solution.
Hi @Anonymous
Thanks for accepting my solution.
Actually I think @ImkeF solution is probably better, therefore I hope you don't mind that I marked her post as a solution as well, for the benefit of other Community members.