Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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.
Hi ,
Days ( hours is good to know too)
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
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?
I am just choosing my startDate, End DAte and holidys column. How can i change this?
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
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
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
Hi Jimmy,
Sorry to take so long to respond, but I am on vacation with limited network access.
The function is working for me. Would you mind posting the code that you called it with so I can see differences?
Thank you,
Mike
May i need to edit the holidays list some way? Now is look like this
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
Where do you see onother query?
Hello @Ritaf
now you changed it (on the other screenshot the query does'nt start on the).
However you have to change the "/fnNetworkdays" into "//fnNetworkdays", because this is only a comment and therefore you have to put the double "/" in it.
All the best
Jimmy
Hi,
i tried to use a code an now is a syntax error😭
Hi jimmy,
Thank you very much for your attempts to help,
The code is realy isn't working.
and error indicator just sendinf me to the with red pen.
ooooooooh this code is for more the 1 options to solution?😳
Syntex now sees ok, but i still have a probelem becuase of empty cells at start tame. Maybe there is some trick to pass them?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
51 | |
19 | |
12 | |
11 |