Forum Discussion

lmarins's avatar
lmarins
Frequent Visitor
4 years ago
Solved

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

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 D...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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 } )
    )
)
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

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's avatar
FrankGaraco
Regular Visitor
3 years ago

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