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(EOMONTH(TODAY(),0)))

8 Replies

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi EmWy24 ,
    It's not complicated to realize this I'll try to use the following DAX in a newly created date table to help you find the number of days worked in each month.

     

    Workdays = 
CALCULATE(
    COUNTROWS('DateTable'),
    'DateTable'[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1),
    'DateTable'[Date] <= EOMONTH(TODAY(), 0),
    WEEKDAY('DateTable'[Date], 2) <= 5
)

     

    Below is the community Blog on how to find the number of working days, one of the real Power query to filter, I hope to help your question, if you have further questions, you can feel free to contact me, I will be the first time to reply when I receive your message!

    Calculate the working days between two dates exclu... - Microsoft Fabric Community

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

     

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

     

     

     

    • EmWy24's avatar
      EmWy24
      Regular Visitor
      1 year ago

      Thank you for the response. Unfortunately this won't work for me, as I don't have a date table and can't have any tables. My source is through Analysis Services.

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

    Hi,

     

    Please try this - just tried it and it works for me.

    CM_WorkdaysInCurrentMonth = 
var start_of_current_month = EOMONTH(today(),-1) + 1
var end_of_current_month = EOMONTH(today(),0)
var calender_month_days = Filter(CALENDAR(start_of_current_month,end_of_current_month), WEEKDAY([Date],2)<6)
RETURN
COUNTROWS(calender_month_days)