Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
5 years ago
Solved

Calculate working Dates between two columns

I have a simple table (1) that contains a Date column. Then I have a second table (2) that contains all holidays also on Date level.

Is there any chance to determine the number of working days - excluding holidays - between the Date column of the first table and today?

If so, how?

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    joshua1990 

    You need to add a custom column with the following M Code in Table 1.

     

     

     

    List.Count(
            if 
                [Date] > Date.From(DateTime.LocalNow())
            then
                {}
            else
            
            List.Select(
                List.Difference(
                    List.Dates(
                        [Date],  
                        Duration.Days
                            (
                                Date.From(DateTime.LocalNow()) - [Date] 
                            ) + 1
                        ,   
                        #duration(1, 0, 0, 0)
                    ),
                    Holidays[Holiday]
                ),
                each Date.DayOfWeek(_,Day.Monday) < 6
            )
        )

     

     

     

     I have attached the file below.
     

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    joshua1990 

    You need to add a custom column with the following M Code in Table 1.

     

     

     

    List.Count(
            if 
                [Date] > Date.From(DateTime.LocalNow())
            then
                {}
            else
            
            List.Select(
                List.Difference(
                    List.Dates(
                        [Date],  
                        Duration.Days
                            (
                                Date.From(DateTime.LocalNow()) - [Date] 
                            ) + 1
                        ,   
                        #duration(1, 0, 0, 0)
                    ),
                    Holidays[Holiday]
                ),
                each Date.DayOfWeek(_,Day.Monday) < 6
            )
        )

     

     

     

     I have attached the file below.
     

    • joshua1990's avatar
      joshua1990
      Post Prodigy
      5 years ago

      Thanks Fowmy : Thanks a lot! But what is about the weekends?

      I'm sorry that I missed to add this information:

      Working days = Mo - Fr

      • Fowmy's avatar
        Fowmy
        Super User
        5 years ago

        joshua1990 

        I added that condition as well also, attached the file after modification. Please check my 1st reply.


    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      CNENFRNL 

       

      I am trying to apply your solution to a simila problem I am having. I downloaded the attachment and followed your instructions, but it is not working. Can you please explain why you create the _Measures query and how it helps?

       

      thank you,