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
Ritaf
Responsive Resident
Responsive Resident

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 .

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

3 ACCEPTED SOLUTIONS
Jimmy801
Community Champion
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 @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

View solution in original post

Jimmy801
Community Champion
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

 

View solution in original post

Jimmy801
Community Champion
Community Champion

😪😭

oooh. .how sad

View solution in original post

37 REPLIES 37
Jimmy801
Community Champion
Community Champion

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

Ritaf
Responsive Resident
Responsive Resident

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.

Jimmy801
Community Champion
Community Champion

Hey
Difference in days i suppose
Jimmy
Ritaf
Responsive Resident
Responsive Resident

Hi , 

Days ( hours is good to know too)

Jimmy801
Community Champion
Community Champion

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

Ritaf
Responsive Resident
Responsive Resident

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?

Jimmy801
Community Champion
Community Champion

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

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

Ritaf
Responsive Resident
Responsive Resident

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 upCapture.JPG

 

Anonymous
Not applicable

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 tablegrafik.png

Have a nice day

Jimmy

Anonymous
Not applicable

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

Ritaf
Responsive Resident
Responsive Resident

May i need to edit the holidays list some way? Now is look like thisCapture.JPG

Jimmy801
Community Champion
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 @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

Ritaf
Responsive Resident
Responsive Resident

Where do you see onother query?Capture.JPG

Jimmy801
Community Champion
Community Champion

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

Ritaf
Responsive Resident
Responsive Resident

Hi,

i tried to use a code an now is a syntax errorCapture.JPG😭

Ritaf
Responsive Resident
Responsive Resident

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.

Capture.JPG

Ritaf
Responsive Resident
Responsive Resident

ooooooooh this code is for more the 1 options to solution?😳 

Ritaf
Responsive Resident
Responsive Resident

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?

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