Calculate net working days and total days between two dates

Hi joshua1990 ,

 

Here is the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcfJCQAgDATAXvJ2wV3vWiT9t6EPIcJ8Zm9TFkEU8/TSwBkbUI1NsMcWOGLMkL5e7auwzP0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Today", each DateTime.LocalNow()),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Today", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Days", each Duration.Days([Today]-[Date])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Dates([Date],[Days],#duration(1, 0, 0, 0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
    #"Added Custom3" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Date.DayOfWeek([Custom],Day.Sunday)),
    #"Grouped Rows" = Table.Group(#"Added Custom3", {"Date"}, {{"Count", each _, type table [Date=nullable date, Today=nullable date, Days=number, Custom=date]}}),
    #"Added Custom4" = Table.AddColumn(#"Grouped Rows", "Mon-Fri", each Table.RowCount(Table.SelectRows([Count], each [Custom.1]<=5 and [Custom.1]>=0))),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Total Days", each Duration.Days(DateTime.Date( DateTime.LocalNow())- [Date])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom5",{"Count"})
in
    #"Removed Columns"

 

Output:

You could check the pbix file attached.

 

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.