Good Afternoon all,
Please need help on solving this one !!!!
I have a table iin_costs that has a list of all the hours employee's worked over a date range. They do a number of tasks throughout the day and there is more than three employees (but for the example i have just used three).
| iin_users.name | reference date | Hours worked | Task |
| John Smith | 22/11/21 | 3.5 | 1 |
| John Smith | 22/11/21 | 1 | 2 |
| John Smith | 22/11/21 | 1 | 2 |
Rebecca Smith | 22/11/21 | 1 | 1 |
| Rebecca Smith | 22/11/21 | 2 | 3 |
| Wayne Smith | 22/11/21 | 3.5 | 1 |
| John Smith | 23/11/21 | 3.5 | 1 |
| John Smith | 23/11/21 | 1 | 2 |
Rebecca Smith | 23/11/21 | 1 | 1 |
| John Smith | 24/11/21 | 3.5 | 1 |
| John Smith | 24/11/21 | 1 | 2 |
Rebecca Smith | 24/11/21 | 1 | 1 |
| Rebecca Smith | 24/11/21 | 2 | 3 |
| Wayne Smith | 24/11/21 | 3.5 | 1 |
| John Smith | 25/11/21 | 1 | 2 |
| John Smith | 26/11/21 | 1 | 2 |
| Rebecca Smith | 26/11/21 | 2 | 3 |
What i would like is a measure so that i can display it on a card and to use for various other calculations that will display the number of employees that were working over any given date range (which will be a silcer)
Eg. for the dates 22/11 through to 26/11 the number of employees that worked were 11 (John Worked 5 days, Rebecca Worked 4 days, Wayne worked 2 days)
I have tried this
Measure = CALCULATE(DISTINCTCOUNT(iin_costs[reference_date]), GROUPBY(iin_costs, iin_costs[reference_date],iin_costs[iin_users.name]))
But that does not give me the count of the number of days John worked + the number of Days that Rebecca worked + the number of days Wayne worked.
It just counts the distinct days that any person showed up for work which is 5.
How do i get to count the number of days my distinct employees worked per employee then summed?
Cheers,
Byron
