cancel
Showing results for
Did you mean:
Post Patron

## NEtwork Days M language

Hi ,

I have to create KPI's based on sla's of orders management. I want to calculate those SLA's in working days at power Query (Not Dax).

I read many purposes to solutions , but didn't succeed in adopting it for my File (because a very weak knowledge of M language).

In a link below is my pbix file with 2 periods for example, may be someone can explain me step by step how can i calculate in just working days without weekends and holidays EndSaleTime-StartSale time and / or

EndSupplyTime-StartSupply Time or just put it on my pbix file .

Thanks a lot,

Rita

3 ACCEPTED SOLUTIONS
Community Champion

Hello

I tried to speed up my version. so if you have no performance issue with this solution, I would go for it.

This solution is slower af the other proposed @mcybulski . But if we come to include holiday it's faster

``````// fnNetWorkDays
(DateStart as date, DateEnd as date, optional Holidays as list) =>
let
Switch = DateStart > DateEnd,
StartDate = if Switch then DateEnd else DateStart,
EndDate = if Switch then DateStart else DateEnd,
WeekDaysInRange=
(
Duration.TotalDays
(
(
EndDate-
(
StartDate
+Duration.From
(
Date.DayOfWeek
(
EndDate
)
-Date.DayOfWeek
(
StartDate
)
)
)
)
)
/7*5
)
+Date.DayOfWeek
(
EndDate
)
-Date.DayOfWeek
(
StartDate
)
+1
+
(
if Date.DayOfWeek
(
StartDate
)=0
then
-1
else
0
)
+
(
if Date.DayOfWeek
(
EndDate
)=6
then
-1
else
0
),
HolidaysInRange = if Holidays = null then {} else
List.Select(Holidays, each StartDate <= _ and _ <= EndDate and Date.DayOfWeek(_, Day.Monday) <5 ),
Result = (if Switch then -1 else 1) * ((WeekDaysInRange) - List.Count(HolidaysInRange))
in
Result

// fnNetWorkDays2
(DateStart as date, DateEnd as date, optional Holidays as list) =>
let
Switch = DateStart > DateEnd,
StartDate = if Switch then DateEnd else DateStart,
EndDate = if Switch then DateStart else DateEnd,
HolidayIntern = if Holidays = null then List.Buffer({}) else List.Buffer(Holidays),
CreateListDates =List.Buffer( List.Dates
(
StartDate,
Duration.TotalDays
(
EndDate - StartDate
)
+1,
#duration(1,0,0,0)
)),

ExcludeWeekend = List.Select
(
CreateListDates,
each
Date.DayOfWeek
(
_,
Day.Monday
)
<5
),

ExcludeHoliday =
if Holidays = null then ExcludeWeekend else List.Difference
(
ExcludeWeekend,
HolidayIntern
),
Result = List.Count
(
ExcludeHoliday
),

FinalResult = try (if Switch then -1 else 1) * (Result) otherwise null

in
FinalResult``````

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

Community Champion

Hey

I was just now that i posted both version.. and this confused you 🙂 sorry 🙂

``````// fnNetWorkDays2
(DateStart as date, DateEnd as date, optional Holidays as list) =>
let
Switch = DateStart > DateEnd,
StartDate = if Switch then DateEnd else DateStart,
EndDate = if Switch then DateStart else DateEnd,
HolidayIntern = if Holidays = null then List.Buffer({}) else List.Buffer(Holidays),
CreateListDates =List.Buffer( List.Dates
(
StartDate,
Duration.TotalDays
(
EndDate - StartDate
)
+1,
#duration(1,0,0,0)
)),

ExcludeWeekend = List.Select
(
CreateListDates,
each
Date.DayOfWeek
(
_,
Day.Monday
)
<5
),

ExcludeHoliday =
if Holidays = null then ExcludeWeekend else List.Difference
(
ExcludeWeekend,
HolidayIntern
),
Result = List.Count
(
ExcludeHoliday
),

FinalResult = try (if Switch then -1 else 1) * (Result) otherwise null

in
FinalResult``````

invoke it like this

=try fn([Start],[End]) otherwise null

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

Community Champion

😪😭

37 REPLIES 37
Community Champion

Hey

I was just now that i posted both version.. and this confused you 🙂 sorry 🙂

``````// fnNetWorkDays2
(DateStart as date, DateEnd as date, optional Holidays as list) =>
let
Switch = DateStart > DateEnd,
StartDate = if Switch then DateEnd else DateStart,
EndDate = if Switch then DateStart else DateEnd,
HolidayIntern = if Holidays = null then List.Buffer({}) else List.Buffer(Holidays),
CreateListDates =List.Buffer( List.Dates
(
StartDate,
Duration.TotalDays
(
EndDate - StartDate
)
+1,
#duration(1,0,0,0)
)),

ExcludeWeekend = List.Select
(
CreateListDates,
each
Date.DayOfWeek
(
_,
Day.Monday
)
<5
),

ExcludeHoliday =
if Holidays = null then ExcludeWeekend else List.Difference
(
ExcludeWeekend,
HolidayIntern
),
Result = List.Count
(
ExcludeHoliday
),

FinalResult = try (if Switch then -1 else 1) * (Result) otherwise null

in
FinalResult``````

invoke it like this

=try fn([Start],[End]) otherwise null

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

Post Patron

Hi Jimmi.

Thank you so much , finally i fell that i have a hope to solve it 🙂

where do ia need to put the code condition "=try fn([Start],[End]) otherwise null"?

Community Champion

Hello @Ritaf

were you able to solve it?

fn stands for your function name

BR

Jimmy

Post Patron
Hi jimmy , unfortunately no. I just gave up and did it with dax 😭
Community Champion

😪😭

Post Patron

I just have to give some time to learn this code, and i will do it.

Solution Specialist

I like how you changed the duration to handle days only and reduce the number of list dates generated. Also like the buffering of the createlist to reduce recalculations plus the use of List.Select. Along with your use of List.Difference, I think your function is a great demonstration on how to use PQ's powerful list functions.

Community Champion

Hello @mcybulski

thank you. I did also like how you used the switch-variable to enable the functionality also to handle differences in -.

yes, when you totaly understand the logic of lists, they are the most powerful object in M.

Have a nice day

Jimmy

Community Champion

hey

but now you can see the other quiry above

delete the upper part and it will work

Jimmy

Community Champion
Hey

You have to create a new query with only my code in it. Then rename query and invoke it from your table in a new column
Post Patron

This is exacly what i did...i am not so bad🤣

And i have an error in synatax...

Community Champion
Hello

But I can see another query above.
Delete it and paste my query.
Good luck
Jimmy
Community Champion

Hello

seems to be fine

just add the column of this table as third parameter like TableName[ColumnName]

Jimmy

Post Patron

Hi,

Thank you very much for your responding.

Unfortunately it isn't working, ,may be because of some blank cells in my start day columns (I cant remove this rows because of orher measures from this data)😭

Community Champion

Hello

invoke the function like this

``try fnNetWorkDays(Date.From([Start]),Date.From([End])) otherwise null``

but be carefull as the code proposed by @mcybulski seems to have some problems

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

Community Champion
For holidays you have to pass a list of days
Community Champion
Hello
It has to be datetimes and last parameter null

Announcements