topic Re: Number of Workdays per Month in DAX Commands and Tips

Number of Workdays per Month

Anonymous — Tue, 20 Aug 2024 23:14:15 GMT

I need to calculate the number of work days per month for some margin and revenue data.

Essentially the invoice date I am using should only include work days, no holidays or weekends.

What formula can I use that would return the number of distinct invoice dates per month?

Re: Number of Workdays per Month

lbendlin — Wed, 21 Aug 2024 01:14:46 GMT

There is no need to calculate this in Power BI. This data is immutable. Use an external reference table where you precomputed that stuff once.

Re: Number of Workdays per Month

ahadkarimi — Sat, 24 Aug 2024 14:51:37 GMT

Hi Anonymous , try two measures below, and if you encounter any issues, let me know.

Workdays = CALCULATE( COUNTROWS('InvoiceTable'), FILTER( 'InvoiceTable', WEEKDAY('InvoiceTable'[InvoiceDate],2) <= 5 && NOT('InvoiceTable'[InvoiceDate] IN VALUES('HolidayTable'[HolidayDate])) ) )

Distinct Workdays per Month = CALCULATE( DISTINCTCOUNT('InvoiceTable'[InvoiceDate]), FILTER( 'InvoiceTable', WEEKDAY('InvoiceTable'[InvoiceDate],2) <= 5 && NOT('InvoiceTable'[InvoiceDate] IN VALUES('HolidayTable'[HolidayDate])) ) )

Did I answer your question? If so, please mark my post as the solution!✔️
Your Kudos are much appreciated! Proud to be a Responsive Resident!

Re: Number of Workdays per Month

Anonymous — Mon, 26 Aug 2024 03:17:37 GMT

Hi Anonymous ,

If you want to return the working days of the month (excluding weekends and holidays) please see this test.
In my scenario:

My Table:

Table = CALENDAR(DATE(2024,1,1),TODAY())

workdays =

VAR _start = DATE(YEAR(MAX([Date])), MONTH(MAX([Date])), 1)

VAR _last = EOMONTH(_start, 0)

RETURN

NETWORKDAYS(_start, _last, 1, {DATE(2024, 1, 1), DATE(2024, 12, 31)})

I set the weekend to Saturday and Sunday, which is represented as 1 in the NETWORKDAYS function.

For more details, you can read related document link:

NETWORKDAYS function (DAX) - DAX | Microsoft Learn

Best Regards,

Sunshine Gu