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

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

 

Ritaf
Responsive Resident
Responsive Resident

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

Ritaf
Responsive Resident
Responsive Resident

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

😪😭

oooh. .how sad

Ritaf
Responsive Resident
Responsive Resident

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

Anonymous
Not applicable

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

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

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

Ritaf
Responsive Resident
Responsive Resident

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

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