Quantity of Worked Days per Month

[Nick1810]

Hi, i have 2 tables. One is the history of services a technician did and a Calendar Table which links the Date in the Technician Services Table.

This is the structure

This is the date of the Services Table (the date is in format dd/mm/yyyy)

Service ID	Service Date	Technician
1	01/03/2022	John
2	05/03/2022	John
3	05/03/2022	John
4	20/03/2022	John
5	02/04/2022	John
6	03/04/2022	John
7	15/04/2022	John
8	15/04/2022	John
9	25/04/2022	John
10	25/04/2022	John
11	25/04/2022	John
12	01/03/2022	Bob
13	04/03/2022	Bob
14	04/03/2022	Bob
15	09/03/2022	Bob
16	09/03/2022	Bob
17	21/03/2022	Bob
18	02/04/2022	Bob
19	15/04/2022	Bob
20	15/04/2022	Bob
21	26/04/2022	Bob
22	26/04/2022	Bob
23	26/04/2022	Bob

What I need is to know the quantity of days per month per technician that effectively worked. The values in bold red are the ones I need to obtain (Q Worked Days)

	March	March	April	April
Technician	Q Services	Q Worked Days	Q Services	Q Worked Days
Bob	6	4	6	3
John	4	3	7	4

Thanks!

Nicolás.

[amitchandak]

@Nick1810 , what do you mean by effective here?

You can use count(Table[Service ID] )

or

countrows(summarize(Table, Table[Service Date]) )

Or you can filter for work day

example

measure =

calculate(count(Table[Service ID] ), filter(Date, weekday(Date[Date],2) < 6 ) )

[Nick1810]

Hi @amitchandak ,

By effective I mean how many days I technician worked despite how many services he did in one day.

I made a measure to count how many services a technician did, like the example you gave me, and that is how a got that Bob did 6 services in march and 6 in april, while John did 4 in march and 7 in april. I can calculate that.

But what I need to obtain is how many days each one worked in a month. If, for example, Bob did 5 services in 1 day, I want the measure to count 1. 

Taking into account the example data I posted, Bob worked 4 days in March and 3 in April, while John worked 3 days in March and 4 in April. This is the data i want to calculate by a measure.

Thanks.

[Anonymous · Accepted solution]

Hi @Nick1810 ,

You can try distinctcount function to count the working days for each people.

Measure:

Q Services = CALCULATE(COUNT(Services[Service Date]))

Q Worked Days = CALCULATE(DISTINCTCOUNT(Services[Service Date]))

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nick1810]

Hi @Anonymous ,

Yes, that worked! I was making it so complicated that wasn´t working. The thing I notice is that to work i don´t have to use Services[Service Date].DATE, but only Services[Service Date].

Many thanks!