cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
frankhofmans
Helper III
Helper III

Count the number of days in a certain period

hi PowerBI experts,

 

i have a data file with the executed tasks of our employees and the date of these tasks. I want to calculate the number of unique days in a certain period in which tasks are executed.

 

I have the following data table:

 

Task IDEmpl. IDDate
11004-01-2021
21004-01-2021
11013-01-2021
31015-01-2021
11018-01-2021
52011-01-2021
22018-01-2021
72018-01-2021
11001-02-2021
51008-02-2021
91008-02-2021
92008-02-2021
12011-02-2021
12012-02-2021
22012-02-2021

 

I want the following outcome:

 

Company level:

 

MonthUnique days tasks executed in selected period
Jan5
Feb4

 

Employee level:

 

Employee ID (jan and feb)Unique days task executed in selected period (jan and feb)
106
205

 

So if a employee performed multiple tasks on 1 day, it has to be calculated as 1 unique day (fe empl. ID 10 on 04-01-2021), and if 2 employees performed tasks on the same day, on general level it has to be calculated as 1 unique day, but on employee level it has to be calculated as 2 (fe 08-02-2021, where empl. ID 10 and 20 both performed tasks).

 

Many thanks for your help!

 

Regards,

 

Frank

1 ACCEPTED SOLUTION

Hi, @frankhofmans 

To  count only the working days, please create a calculated column as below

Column = IF(WEEKDAY('Data Table'[Date],2)IN{1,2,3,4,5}, 1, blank())

 And then add a  filter condition to your original measure as follows:

Unique days tasks executed in selected period =
CALCULATE (
    DISTINCTCOUNT ( 'Data Table'[Date] ),
    FILTER ( 'Data Table', 'Data Table'[Column] = 1 )
)

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
frankhofmans
Helper III
Helper III

Hi jdbuchanan71,

 

This works, but is there a way to count only the working days? I added a column (if date = weekday, 1, blank()) and did the distinctcount (on the new column), but i still get weekend results (strange, cause in the new colums, there aren't any weekend dates).

 

Thanks!

Hi, @frankhofmans 

To  count only the working days, please create a calculated column as below

Column = IF(WEEKDAY('Data Table'[Date],2)IN{1,2,3,4,5}, 1, blank())

 And then add a  filter condition to your original measure as follows:

Unique days tasks executed in selected period =
CALCULATE (
    DISTINCTCOUNT ( 'Data Table'[Date] ),
    FILTER ( 'Data Table', 'Data Table'[Column] = 1 )
)

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

jdbuchanan71
Super User II
Super User II

@frankhofmans 

Give this a try.

Unique days tasks executed in selected period = DISTINCTCOUNT ( 'data table'[Date] )

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors