Calculate Working Date Between two dates Error

[MateoBI]

Hello developpers,

I use the fonction for calculate the number of working date between two dates.

The function work but not all time. He doesn't work when StartDate > EndDate.

The function : 

(StartDate as date, EndDate as date, Holidays as list) as number =>
let
DateList = List.Dates(StartDate, Number.From(EndDate- StartDate), #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, Holidays),
CountDays = List.Count(RemoveHolidays)
in
Number.Abs(CountDays)

The result : 

Help me plz ...

[Anonymous · Accepted solution]

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. 

[amitchandak]

@MateoBI , would you like to use newtorkday DAX function ?

Power BI - Business day with and without using DAX Function NETWORKDAYS: https://www.youtube.com/watch?v=Qs03ZZXXE_c

https://amitchandak.medium.com/power-bi-dax-function-networkdays-5c8e4aca38c