- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Calculating Number of Workdays for Each Month for a Year

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-20-2017 06:40 AM

Hi, power BI community. Need some favor to calculate number of workdays for each month for a certain year. Below is the link of the attached pbix file.

https://drive.google.com/file/d/0B0XwYvmbIN12M3ZLNlAwM0NVVFk/view?usp=sharing

In the file, i already have a mechanism to find out whether a certain day is holiday or not. Saturdays and sundays are considered holidays. In the resource time phased data table, working days column capture whether a certain a day is a working day or not (1 is a working day, 0 is a holiday).

There is also an "Is working" column in the date table. What i need to calculate is to get total working days for each month for a specific year. I tried to use working days= calculate(distinct count(resource demand timephased(working days) but its not working. Any prompt solution will be highly appreciated. Thanks

Regards

Hasan

Solved! Go to Solution.

Accepted Solutions

## Re: Calculating Number of Workdays for Each Month for a Year

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-21-2017 12:48 AM

You need to use the Year and Month from your Date table in your slicer. I have tested it, here is the report looks like.

Cretae a measure in Date table.

WorkingDay = CALCULATE(SUM('Date'[IsWorkDay]))

Regards,

Charlie Liao

All Replies

## Re: Calculating Number of Workdays for Each Month for a Year

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-20-2017 06:54 AM

if a working day = 1 and a non one is 0 why can't you just sum it as opposed to doing a distinct count?

## Re: Calculating Number of Workdays for Each Month for a Year

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-20-2017 07:05 AM

Hi. thanks for the quick reply. Yes I have summed but when i add the values to the table, it shows total value but when i create different table using values from data table, it works but i need it to work with the main table. here is the attached image of the situation. Thanks

## Re: Calculating Number of Workdays for Each Month for a Year

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-21-2017 12:48 AM

You need to use the Year and Month from your Date table in your slicer. I have tested it, here is the report looks like.

Cretae a measure in Date table.

WorkingDay = CALCULATE(SUM('Date'[IsWorkDay]))

Regards,

Charlie Liao

## Re: Calculating Number of Workdays for Each Month for a Year

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-21-2017 02:10 AM

@v-caliao-msft thanks a lot for the solution. I added month and date from the date table and it looks like this now. But is there a reason why the month and year from other tables are not working here? Thanks

Hasan