topic Re: Calculate working days for a month (excluding Holidays and Weekend) in DAX Commands and Tips

Calculate working days for a month (excluding Holidays and Weekend)

Anonymous — Wed, 16 Dec 2020 16:24:00 GMT

Hi,

I want to calculate working days for a month in the calendar Table and have that measure applied to another Table metrics in PowerBI.

The Holidays are listed in a separate table from the Calendar Table. The Calendar Table already has a column - Count of Working days (excluding weekends).

Can someone help how to further exclude holidays in this Count of working days? thanks!

Re: Calculate working days for a month (excluding Holidays and Weekend)

Greg_Deckler — Wed, 16 Dec 2020 16:29:45 GMT

I wrote something for this once: Net Work Days (powerbi.com)

There is an improved version in my book DAX Cookbook, you can get it here: GitHub - gdeckler/DAXCookbook

Recipe 10 in Chapter 2

Re: Calculate working days for a month (excluding Holidays and Weekend)

Anonymous — Thu, 17 Dec 2020 13:58:26 GMT

Thank you Greg_Deckler .

To further explain, below are the current columns in my calendar table. Also,

I have a measure in table 2 other than the Calendar table (relationship exists between both of these tables) and built a measure which is Total Days in Month = DISTINCTCOUNTNOBLANK('Table 2 - Metrics'[DateReceived].[Day]). This gives me total calendar days for a particular month. However, the challenge is to only count Working days (excluding holidays and Weekends). There is a relationship via date between the Holidays table and the Calendar table as well. Is there any simple measure to apply? Thanks!

Re: Calculate working days for a month (excluding Holidays and Weekend)

Greg_Deckler — Thu, 17 Dec 2020 14:43:52 GMT

Anonymous So I would do something along the lines of the following:

Measure = VAR __Table = FILTER('Calendar',[Working Days]=1) RETURN COUNTROWS(DISTINCT(EXCEPT(__Table,'Holidays')))