Calculating Number of Workdays for Each Month for a Year

[Hasan_iba]

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

[vanessafvg]

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?

[Hasan_iba]

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

[v-caliao-msft · Accepted solution]

@Hasan_iba,

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

[Hasan_iba]

@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