Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
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
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"?
😪😭
oooh. .how sad
I just have to give some time to learn this code, and i will do it.
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.
Hello @Anonymous
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
hey
but now you can see the other quiry above
delete the upper part and it will work
Jimmy
This is exacly what i did...i am not so bad🤣
And i have an error in synatax...
Hello
seems to be fine
just add the column of this table as third parameter like TableName[ColumnName]
Jimmy
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)😭
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