topic Column count working days (without weekend/holidays) between two dates columns in the same table in DAX Commands and Tips

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

lmarins — Tue, 14 Jun 2022 21:17:03 GMT

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]

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

Jihwan_Kim — Wed, 15 Jun 2022 02:51:51 GMT

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 } ) ) )

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

v-yanjiang-msft — Tue, 21 Jun 2022 07:52:11 GMT

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.

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

lmarins — Fri, 24 Jun 2022 17:54:40 GMT

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.

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

FrankGaraco — Wed, 31 May 2023 23:03:50 GMT

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