## Desktop

Highlighted
Regular Visitor
Posts: 38
Registered: ‎04-07-2017

# Calculating Number of Workdays for Each Month for a Year

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.

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

Accepted Solutions
Moderator
Posts: 1,386
Registered: ‎03-06-2016

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

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
Super Contributor
Posts: 1,318
Registered: ‎07-27-2016

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

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?

Regular Visitor
Posts: 38
Registered: ‎04-07-2017

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

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

Moderator
Posts: 1,386
Registered: ‎03-06-2016

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

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

Regular Visitor
Posts: 38
Registered: ‎04-07-2017

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

@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