Forum Discussion

EmilyM2019's avatar
EmilyM2019
Helper II
6 years ago

Working days date count

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. 

Need Help

6 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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's avatar
      EmilyM2019
      Helper II
      6 years ago

      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's avatar
        Jimmy801
        Community Champion
        6 years ago

        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

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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