Calculate number of work days

[TinyElephant]

Hello,

I have gone through some of the posts on this forum. Please have a look at my table.

I want to calculate the number of work days for the series of "Date", i.e. exclude Saturdays, Sundays, and "Holiday" (in Work column). There may not be a holiday in a particular Date Range.

Employee	Work	Hours	Date
Person 1	Task 1	4	09-Aug-21
Person 1	No Work	2	09-Aug-21
Person 1	Task 2	2	09-Aug-21
Person 2	Task1	3	09-Aug-21
Person 2	Task 2	1	09-Aug-21
Person 2	Leave	4	09-Aug-21
Person 3	No Work	3	09-Aug-21
Person 3	Task 2	5	09-Aug-21
Person 1	Holiday	8	10-Aug-21
Person 2	Holiday	8	10-Aug-21
Person 3	Holiday	8	10-Aug-21
Person 1	Task 2	8	11-Aug-21
Person 2	Task 3	8	11-Aug-21
Person 3	Task 4	8	11-Aug-21
Person 1	Task 2	8	12-Aug-21
Person 2	Task 3	8	12-Aug-21
Person 3	Task 2	8	12-Aug-21

[amitchandak]

@TinyElephant , between which range you wany workday?

Not very clear with example

[TinyElephant]

The dates are mentioned in the Date column. In the image here you can see the date entries from 09 Aug 2021 to 12 Aug 2021. I don't want to manually mention the start and end date but to automatically pick it up from the Date column.
Later I would like to control the dashboard visuals using a Date Ranger slider.

[v-yiruan-msft · Accepted solution]

Hi @TinyElephant ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

Create the below 2 measures to get the number of work days:

Number of work days = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Date] ),
    FILTER (
        'Table',
        'Table'[Work] <> "Holiday"
            && WEEKDAY ( 'Table'[Date], 2 ) < 6
    )
)

Measure = SUMX(VALUES('Table'[Employee]),[Number of work days])

Best Regards