cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Ritaf Member
Member

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

Accepted Solutions
Jimmy801 New Contributor
New Contributor

Re: NEtwork Days M language

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

Re: NEtwork Days M language

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

Re: NEtwork Days M language

😪😭

oooh. .how sad

View solution in original post

37 REPLIES 37
Jimmy801 New Contributor
New Contributor

Re: NEtwork Days M language

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

Re: NEtwork Days M language

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

Re: NEtwork Days M language

Hey
Difference in days i suppose
Jimmy
Ritaf Member
Member

Re: NEtwork Days M language

Hi , 

Days ( hours is good to know too)

Jimmy801 New Contributor
New Contributor

Re: NEtwork Days M language

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

Re: NEtwork Days M language

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

Re: NEtwork Days M language

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

Re: NEtwork Days M language

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

Jimmy801 New Contributor
New Contributor

Re: NEtwork Days M language

Hello
It has to be datetimes and last parameter null

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)