NEtwork Days M language

[Ritaf]

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

[Jimmy801 · Accepted 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

[Jimmy801 · Accepted solution]

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