Forum Discussion

EmWy24's avatar
EmWy24
Regular Visitor
1 year ago

Workdays in current Month

I am trying to work out the number of work days in the current month.   I have a formula which works to show number of days in month, but how do I change this to just show weekdays?   DAY(DAY(EO...
pbiuseruk's avatar
pbiuseruk
Icon for Resolver IV rankResolver IV
1 year ago

There's a function called NETWORKDAYS.

These are the parameters NETWORKDAYS(<start_date>, <end_date>[, <weekend>, <holidays>]). By default it excludes weekends. You just need to do something like this:

NETWORKDAYS(<StartDateOfMonth>,<EndDateOfMonth>)

EmWy24's avatar
EmWy24
Regular Visitor
1 year ago

Thank you, but unfortunately NETWORKDAYS is not an option in my version of Power BI 😞

  • pbiuseruk's avatar
    pbiuseruk
    Icon for Resolver IV rankResolver IV
    1 year ago

    Ahh, to be honest, I'd strongly recommend updating it or asking your admins to because that's crazy.

    But if you can't then another way to do this (although I'm not the biggest fan of this), is to make a day column in Power Query (really simple to do - just select the date field in your calender table, then fo to add columns tab at the top, select the dates dropdown on the top right and then select Day).

    ^This will add a Day field to the calender table like Monday, Tuesday, etc..

    Then in Power Query, make a conditional column and then say If Monday, then Weekday, If Tuesday, then Weekday, etc...

    Then in your model, make sure that you do it in a way where you link the calendar table to any dates fields in other tables and then you'll be able to use that column as a filter in your pages.

    Hope that makes sense.

  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    1 year ago

    o calculate the number of workdays (weekdays) in the current month in Power BI, you can use DAX to account for weekdays without needing the NETWORKDAYS function.

    Here's a DAX formula that calculates the number of weekdays in the current month:

     

    DAX
    Copy code
    Workdays_CurrentMonth = VAR StartOfMonth = DATE(YEAR(TODAY()), MONTH(TODAY()), 1) VAR EndOfMonth = EOMONTH(TODAY(), 0) VAR DaysList = GENERATESERIES(StartOfMonth, EndOfMonth, 1) RETURN COUNTX( FILTER( DaysList, WEEKDAY([Value], 2) <= 5 -- This filters out weekends (Saturday and Sunday) ), [Value] )
     

    Explanation:

    1. StartOfMonth calculates the first day of the current month.
    2. EndOfMonth uses EOMONTH to get the last day of the current month.
    3. GENERATESERIES creates a list of all dates between StartOfMonth and EndOfMonth.
    4. WEEKDAY([Value], 2) checks if the day is a weekday (Monday to Friday), where 2 means the week starts on Monday.
    5. FILTER is used to exclude weekends.
    6. Finally, COUNTX counts the number of weekdays.

    This formula will return the number of workdays in the current month.