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 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]

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

4 Replies

  • lmarins's avatar
    lmarins
    Frequent Visitor
    4 years ago

    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.

  • 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 

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.