Calculate working Dates between two columns

[joshua1990]

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?

[CNENFRNL]

[Anonymous]

@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,

[Fowmy · Accepted solution]

@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]

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]

@joshua1990 

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