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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

NETWORKDAYS in PowerQuery Editor

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. 

2 ACCEPTED SOLUTIONS

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

View solution in original post

Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

Please see the attached file with a solution.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

View solution in original post

9 REPLIES 9
Syndicate_Admin
Administrator
Administrator

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?

 

Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

Please see the attached file with a solution.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Anonymous
Not applicable

Thank you for a this solution. 

Greg_Deckler
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Anonymous
Not applicable

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.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors