Working days date count

[EmilyM2019]

Good morning,

I am working on a report where I need to track the number of working days from the point an activity started. 

I did some Googling and watched a YouTube video and came up with a solution where I created a blank query using some code provided by the video author. For those who are interested here is the code I used:

= (InitialDate as date, FinalDate as date ) as number =>
let
    DaysBetweenDates = Duration.Days(FinalDate-InitialDate),
    DaysList = List.Dates(List.Min({InitialDate,FinalDate}), Number.Abs(DaysBetweenDates)+1, Duration.From(1)),
    WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ),
    WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList)
in
    WorkingDays

Then in the power query with the rest of my data I created a new column using the "invoke custom function" option. Here is the code for that column.

= Table.AddColumn(#"Changed Type1", "Working days count", each #"Working days query"([#"Changed On (Task)"], #date(2020, 2, 3))) 

The problem that I am having with this, which sounds simple when I say it but I just can't figure it out, is that I want the column to dynamically calulate the total number of working days from start date (aka changed on (task) to today. The above, which I created yesterday, I have had to manually change the date to today.

Any thought's?

TIA, Emily. 

[Jimmy801]

Hello @EmilyM2019 

have you been able to solve the problem with the replies given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[Jimmy801]

Hello

you can use my function I've developed some time ago. This is the complete function

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

Here a small practical example how to invoke the function "fnNetWorkDays2"

let
	Source = #table
	(
		{"Task","Start","End"},
		{
			{"1","43831","43905"},	{"2","43525","44122"}
		}
	),
    ToDate = Table.TransformColumns
    (
        Source,
        {
            {
                "Start",
                each Date.From(Number.From(_)),
                type date
            },
            {
                "End",
                each Date.From(Number.From(_)),
                type date
            }
        }
    ),
    AddWorkingDays = Table.AddColumn
    (
        ToDate,
        "WorkingDays",
        each fnNetWorkDays2([Start],Date.From(DateTime.FixedLocalNow())),
        type number
    )
in
	AddWorkingDays

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[EmilyM2019]

Hi @Jimmy801 ,

Thanks for coming back to me. I'm so new at this and I don't really understand your response. Sorry.

I know from other posts that I the syntax that I need for the today M query is (DateTime.LocalNow() 

But for me that is still erroring.

This is what I have changed my code to:

= Table.AddColumn(#"Changed Type1", "Working days count", each #"Working days query"([#"Changed On (Task)"], DateTime.FixedLocalNow()))

But I can't understand why this isn't working.

[Jimmy801]

Hello @EmilyM2019 

first of all please copy my first code to a new blank query and name the query fnNetWorkDays2

then change your code as follows

= Table.AddColumn(#"Changed Type1", "Working days count", each fnNetWorkDays2([#"Changed On (Task)"], DateTime.FixedLocalNow()))

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[EmilyM2019]

Hi @Jimmy801 

When I copy and paste your code into a new blank query it doesn't load in as an fx query, it shows as ABC and all the text is in red.

What am I doing wrong?

Thanks, Emily.

[Jimmy801]

Hello @EmilyM2019 

most probably you are copying into the formula bar. 

Open the advanced editor, delete everything and paste my first code

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy