Reply
Highlighted
Regular Visitor
Posts: 36
Registered: ‎04-07-2017
Accepted Solution

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. 

 

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


Accepted Solutions
Moderator
Posts: 949
Registered: ‎03-06-2016

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

@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]))

Capture.PNG

 

Regards,

Charlie Liao

View solution in original post


All Replies
Super Contributor
Posts: 874
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: 36
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

Untitled.png

Moderator
Posts: 949
Registered: ‎03-06-2016

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

@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]))

Capture.PNG

 

Regards,

Charlie Liao

Regular Visitor
Posts: 36
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

Untitled.png