topic Re: NEtwork Days M language in Power Query

NEtwork Days M language

Ritaf — Sat, 07 Dec 2019 01:53:03 GMT

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 .

https://drive.google.com/drive/folders/1xQRM3C9q5uoTs47lYv7FoM5Y3vqZQy2l?usp=sharing

please don't send me to links of similar questions and solutions because i read them .

Thanks a lot,

Rita

Re: NEtwork Days M language

Jimmy801 — Sat, 07 Dec 2019 15:46:47 GMT

Hello Ritaf

I understand what exactly you need.. what are sla? What do you want to calculate exactly?

If you could provide some data example with the desired output with a detailed description of it or the final goal would help and be appreciated.

by the way is a Power-BI-file saved as .xlsx

Have a nice time

Jimmy

Re: NEtwork Days M language

Ritaf — Sat, 07 Dec 2019 16:30:24 GMT

Hi Jimmy,

I just want to calculate a difference bertween 2 dates without weekends snd/ or holidays , for this cse study i not matter which, i just need to understend the code. And thank you for your paying attention i put a pbix file.

Re: NEtwork Days M language

Jimmy801 — Sat, 07 Dec 2019 17:27:06 GMT

Hey
Difference in days i suppose
Jimmy

Re: NEtwork Days M language

Ritaf — Sat, 07 Dec 2019 17:29:03 GMT

Hi ,

Days ( hours is good to know too)

Re: NEtwork Days M language

Jimmy801 — Sat, 07 Dec 2019 17:36:45 GMT

Hello

this function gives back the diffence in hours, excluding the weekends.

You have to pass starttime and endtime. You can pass optionally a list with holidays to exclude them to

(StartTime as datetime, EndTime as datetime, ListHoliday as nullable list) as number => let //StartTime = #datetime(2019,12,3,8,00,00), //EndTime = #datetime(2019, 12, 8, 8, 0, 0), //ListHoliday = {"03.12.19", "04.12.19"}, ListHolidayCheck = if ListHoliday = null then {} else ListHoliday, ListHolidayInternal = List.Transform(ListHolidayCheck, each Date.From(_)), DurationInHours = (Number.From(EndTime)-Number.From(StartTime))*24, DateTimesFromStartEnd = List.DateTimes(StartTime, DurationInHours, #duration(0,1,0,0)), FilterHolidays = List.Transform(DateTimesFromStartEnd, each if List.Contains(ListHolidayInternal, Date.From(_))= true then null else _), TableWithDateTime = #table({"DateTime"}, List.Zip({FilterHolidays})), #"Added Custom" = Table.AddColumn(TableWithDateTime, "Hours", each 1), #"Inserted Day of Week" = Table.AddColumn(#"Added Custom", "Day of Week", each Date.DayOfWeek([DateTime], Day.Monday), Int64.Type), #"Filtered Rows" = Table.SelectRows(#"Inserted Day of Week", each ([Day of Week] < 5)), HoursSum = List.Sum(#"Filtered Rows"[Hours]) in HoursSum

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

Jimmy

Re: NEtwork Days M language

Ritaf — Sat, 07 Dec 2019 17:49:51 GMT

Hi, this is one of solutions i tried an i get an error:

Expression.Error: We cannot convert the value null to type DateTime.
Details:
Value=
Type=[Type]

How can i solve that?

Re: NEtwork Days M language

Jimmy801 — Sat, 07 Dec 2019 17:52:52 GMT

Hello
Maybe you are invoking the function with null in the first or second parameter or try to pass null as third parameter
Jimmy

Re: NEtwork Days M language

Ritaf — Sat, 07 Dec 2019 17:59:55 GMT

I am just choosing my startDate, End DAte and holidys column. How can i change this?

Re: NEtwork Days M language

Jimmy801 — Sat, 07 Dec 2019 18:21:47 GMT

Hello
It has to be datetimes and last parameter null

Re: NEtwork Days M language

Ritaf — Sat, 07 Dec 2019 18:23:23 GMT

Hi again,

i played with this and saw that i have sum null values in startDate, i replace it with 01/01/01 01:00:00 it seems worked but now i think i have some problem with holidays, i see that this calculion takes a lot of time and see strange message (attached) and this gigabytes is continue to growing up

Re: NEtwork Days M language

Jimmy801 — Sat, 07 Dec 2019 18:29:45 GMT

Please share the file
For holidays you have to pass a list of days

Re: NEtwork Days M language

Anonymous — Sun, 08 Dec 2019 08:54:03 GMT

The function below takes a different approach to the issue. It should be fast and small because it does not generate dates.

(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

Re: NEtwork Days M language

Ritaf — Sun, 08 Dec 2019 10:11:43 GMT

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)😭

Re: NEtwork Days M language

Jimmy801 — Sun, 08 Dec 2019 10:28:54 GMT

Hello

this is for sure the better approach as i hasn't to create any list that will slow down for sure.

I've gave it a test and it seems that something is not quite ok. Check out this table

Have a nice day

Jimmy

Re: NEtwork Days M language

Jimmy801 — Sun, 08 Dec 2019 10:34:54 GMT

Hello

invoke the function like this

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

but be carefull as the code proposed by Anonymous 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

Re: NEtwork Days M language

Ritaf — Sun, 08 Dec 2019 10:38:51 GMT

May i need to edit the holidays list some way? Now is look like this

Re: NEtwork Days M language

Jimmy801 — Sun, 08 Dec 2019 10:47:09 GMT

Hello

seems to be fine

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

Jimmy

Re: NEtwork Days M language

Jimmy801 — Sun, 08 Dec 2019 11:41:20 GMT

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 Anonymous . 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

Re: NEtwork Days M language

Ritaf — Sun, 08 Dec 2019 12:34:16 GMT

Hi,

i tried to use a code an now is a syntax error😭