Column count working days (without weekend/holidays) between two dates columns in the same table

[lmarins]

Hello!
I need to create a new column in a table in Power BI that contains the count of business days (excluding weekends and holidays) between two date columns in the same table.
I tried with the DateDiff function but this function gives a total of days and we cannot modify it.
Can I try using the GENERATESERIES function at runtime, to create the range of days and then evaluate the valid days for each row?
This would create a table with all the ranges between every two elements (start date and end date) in the row.

The example is in the format dd/mm/yyyy and the Dif column is the result of DateDiff (Don´t work!).

I need help please!

Thanks and regards!

[lmarins]

[lmarins]

Hello Jihwan Kim!
Thank you very much, your answer solves my doubt.

I will try to associate the Dates table to this query and exclude holidays from the total days.
Greetings.

[v-yanjiang-msft]

Hi @lmarins ,

Does your problem be resolved? Do you need more further help?

Here's a blog about Using-DAX-to-create-a-calendar-table-with-holidays

If your problem has been resolved, please kindly accept the helpful replies as solutions. If not, please feel free to let me know your difficulties.

Best Regards,
Community Support Team _ kalyj

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

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create a sample pbix file like below.

It is for creating a new column, and it only counts days without weekends.
In my opinion, if you want to exclude your public holiday, having a dim-calendar table can help.

Diff count without weekend CC =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            GENERATESERIES ( Data[StartDate], Data[EndDate] ),
            "@weekday", WEEKDAY ( [Value], 2 )
        ),
        NOT ( [@weekday] IN { 6, 7 } )
    )
)

[FrankGaraco]

Buen dia estimado, espero me pueda apoyar, tengo el mismo caso y utilice esta funcion solo que mis campos que contiene las fechas tienen algunas filas vacias y me da error, sabe si hay manera de omitir los vacios ?

De antemano gracias