cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ritaf
Post Patron
Post Patron

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 @mcybulski . 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

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

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"?

Jimmy801
Community Champion
Community Champion

Hello @Ritaf 

were you able to solve it?

fn stands for your function name

 

BR

Jimmy

Hi jimmy , unfortunately no. I just gave up and did it with dax 😭
Jimmy801
Community Champion
Community Champion

😪😭

oooh. .how sad

View solution in original post

I just have to give some time to learn this code, and i will do it.

mcybulski
Solution Specialist
Solution Specialist

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.

Jimmy801
Community Champion
Community Champion

Hello @mcybulski 

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

Jimmy801
Community Champion
Community Champion

hey

 

but now you can see the other quiry above

delete the upper part and it will work

 

Jimmy

Jimmy801
Community Champion
Community Champion

Hey

You have to create a new query with only my code in it. Then rename query and invoke it from your table in a new column

Capture.JPGThis is exacly what i did...i am not so bad🤣

And i have an error in synatax...

Jimmy801
Community Champion
Community Champion

Hello

But I can see another query above.
Delete it and paste my query.
Good luck
Jimmy
Jimmy801
Community Champion
Community Champion

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)😭Capture.JPG

Jimmy801
Community Champion
Community Champion

Hello

 

invoke the function like this

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

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

Jimmy801
Community Champion
Community Champion

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

Hello
It has to be datetimes and last parameter null

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors