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
Anonymous
Not applicable

A M-Code equivalent for =NETWORKDAYS() in excel?

So I have a start and an end date and I need to calculate turnaround time in working days.

Excel has NETWORKDAYS which does the removing of weekends all by itself. 


Is there an equivalent for M-Code? I have googled and can only find complex code to work out days, weeks, weekends etc and subtracting these.

 

Thanks!

Jemma

1 ACCEPTED SOLUTION

You would invoke it in your original table as Add Columns->Invoke Custom Function. Set each argument as column and use your start date column and end date column.

 

Edit: Or alternately use it in Advanced Editor, and pass on Start & End as variable (from calculation, another query etc). 

 

Oh, custom function should be copied and pasted into new blank query (using advanced editor). Name it something that makes sense. (Ex: fnWorkday).

View solution in original post

5 REPLIES 5
Chihiro
Solution Sage
Solution Sage

No built-in "M" function to do it. You can create custom function like below.

Ex: Week starting Monday, Sat & Sun as weekend.

(sDate as date, eDate as date)=>
let
    vDur = Duration.Days(Duration.From(eDate - sDate)),
    Source = List.Dates(sDate, vDur + 1, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Weekday", each Date.DayOfWeek([Column1],Day.Monday)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Weekday] <> 5 and [Weekday] <> 6)),
    Row_Count = Table.RowCount(#"Filtered Rows")
in
    Row_Count

 

Edit: There are other ways to write custom function (such as using List.Generate/List.Accumulate), but above should be easier to maintain/change if you needed to.

Thanks! I suspect this is something I'm expected to do in DAX but I can think in M, DAX gives me headaches 🙂

 

Anonymous
Not applicable

Hello again Chihiro,

 

Where should I insert your formula? Into a new custom column within Query Editor? 

 

I am trying to avoid the creation of a new table with a row of dates - it seems a fairly inefficient way of doing something so I don't mind a function even if it is complex if it does the job in one column or measure!

 

🙂

Jemma

You would invoke it in your original table as Add Columns->Invoke Custom Function. Set each argument as column and use your start date column and end date column.

 

Edit: Or alternately use it in Advanced Editor, and pass on Start & End as variable (from calculation, another query etc). 

 

Oh, custom function should be copied and pasted into new blank query (using advanced editor). Name it something that makes sense. (Ex: fnWorkday).

damonsmithies
Regular Visitor

Could you not create a custom dates table and denote when a date is a weekend and when it is a weekday?

 

See @BhaveshPatel solution here: https://community.powerbi.com/t5/Desktop/Date-Duration-exclude-weekends/td-p/87735

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.