Number of Workdays per Month

[CiaraCaryl]

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?

[Anonymous · Accepted solution]

Hi @CiaraCaryl ,

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

[ahadkarimi]

Hi @CiaraCaryl , 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!

[lbendlin]

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.