topic Re: Workdays in current Month in DAX Commands and Tips

Workdays in current Month

EmWy24 — Wed, 25 Sep 2024 12:45:38 GMT

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

Re: Workdays in current Month

pbiuseruk — Wed, 25 Sep 2024 12:52:19 GMT

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

Re: Workdays in current Month

EmWy24 — Wed, 25 Sep 2024 12:54:39 GMT

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

Re: Workdays in current Month

pbiuseruk — Wed, 25 Sep 2024 13:00:30 GMT

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.

Re: Workdays in current Month

SachinNandanwar — Wed, 25 Sep 2024 13:01:39 GMT

IF(WEEKDAY([Date], 2) >= 6, TRUE(), FALSE())

Re: Workdays in current Month

123abc — Wed, 25 Sep 2024 15:13:34 GMT

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:

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

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

Re: Workdays in current Month

Anonymous — Thu, 26 Sep 2024 05:10:32 GMT

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.

Re: Workdays in current Month

EmWy24 — Thu, 26 Sep 2024 08:33:12 GMT

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.

Re: Workdays in current Month

pbiuseruk — Thu, 26 Sep 2024 09:07:10 GMT

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)