Calculate Working Date Between two dates Error

Hi MateoBI ,

 

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

Holidays:

Table1:

(2) Open Advanced Editor and paste the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBLCoAwDIThu2TdwCTa11lK738NlZSKwSyHj38zYxCEUVmFEinUBmimW/QrzzQ4XNJ2cjopjGySnXSW1ZRQaigtlB6JIBR/wSv6K/MC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Count(
            if 
                [StartDate] >[EndDate]
            then
            
                  List.Select(
                List.Difference(
                    List.Dates(
                        [EndDate],  
                        Duration.Days
                            (
                                [StartDate] - [EndDate]
                            ) + 1
                        ,   
                        #duration(1, 0, 0, 0)
                    ),
                    Holidays[Holiday]
                ),
                each Date.DayOfWeek(_,Day.Monday) < 5
            )
            
            else
            
            List.Select(
                List.Difference(
                    List.Dates(
                        [StartDate],  
                        Duration.Days
                            (
                                [EndDate] - [StartDate]
                            ) + 1
                        ,   
                        #duration(1, 0, 0, 0)
                    ),
                    Holidays[Holiday]
                ),
                each Date.DayOfWeek(_,Day.Monday) < 5
            )
        )),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each -[Custom]),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [StartDate] <= [EndDate] then [Custom] else [Custom.1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Custom", "Custom.1"})
in
    #"Removed Columns"

(3)Or we can create a calculated column.

Work Days 2 = NETWORKDAYS('Table1'[StartDate],'Table1'[EndDate],1, distinct('Holidays'[Holiday])) 

(4) Then the result is as follows.

Please refer to my PBIX.

 

Please refer to the following document for more information.

Power BI: DAX Function NETWORKDAYS | by Amit Chandak | Medium

Calculate Days between dates using Power Query / Power BI — The Power User

Power Query M code - find number of working days b... - Microsoft Power BI Community

 

Best Regards,

Neeko Tang

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