Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am trying to find the average number of hours worked per pay period for a group of employees. If I have a Relative Date filter over the last year, my average is currently counting in the 0 hours (non-existing recorded time rows) for people who were not hired until less than a year ago. I would like to count these people in the average but only include them on and after their hire date.
I have a table that records the submitted timesheets that is connected to another table with employee information. For sensitivity purposes, I will call them Employee Time table and Employees table. Note: In the Employees Table, I have a column with [Hire Date] that gives employees hire dates. I do also have a date table = CALENDARAUTO()
First, in the Employee Table, I have the following measure:
[SumOfPersonnel] = CALCULATE(COUNT(Employee[EmployeeID]), FILTER(Employee, Employee[Active?] = "Active"))
To get the average now, I write the following measures in the Employee Time table:
1. [HRsQuantity] =
VAR MaxDate = MAX(Dates[Date])
RETURN
CALCULATE(SUM('Employee Time'[HoursQuantity]), FILTER('Employees Time', 'Employees Time'[PostingPeriod] <= MaxDate))
2. [AverageTimeAll] = DIVIDE([HRsQuantity], [SumOfPersonnel])
3. [PayPeriodCount] = CALCULATE(DISTINCTCOUNT('Employee Time'[PayPeriod]))
4. [SelectPeriodAvg] =
VAR AvgInPeriod = [AverageTimeAll]/[PayPeriodCount]
RETURN
IF(AvgInPeriod > 0, AvgInPeriod, 0)
I would appreciate the help finding where to implement the [Hire Date] to include Employees in the [SelectPeriodAvg] measure on and after their start date (regardless of date filter).
Looking forward to the help.
Connor
Hi @cmaloyb ,
Are you trying to get the number of employees who have been with the company for more than the average number of years of service? I'm sorry for not understanding your needs very well. Could you please provide some dummy data from the tables involved and then list one or two examples based on these dummy data based on real situations to illustrate the final result you want. It is better if you can provide a simiple sample pbix file with dummy data. Thank you.
Best Regards
Hi @v-yiruan-msft ,
After creating my exampl .pbix file, it seems that I have a different issue than I thought I originally had. I have written my measures incorrectly to do what I'd like.
I am looking for a way to look at the average time recorded at each PayPeriod and then look at the overall average across the selected PayPeriods based on a dynamic Date Slicer. I am not able to attach a .pbix file, assuming my user permissions, so I will screenshot and provide tables here of what I have done with dummy data.
Employee Table
PayPeriod
EmployeeID | HoursQuantity | Employees Time Table |
ID001 | 38 | 1/15/2021 |
ID001 | 40 | 2/15/2021 |
ID001 | 40 | 3/15/2021 |
ID001 | 25 | 4/15/2021 |
ID001 | 42 | 5/15/2021 |
ID001 | 40 | 6/15/2021 |
ID001 | 45 | 7/15/2021 |
ID002 | 40 | 2/15/2021 |
ID002 | 41 | 3/15/2021 |
ID002 | 35 | 4/15/2021 |
ID002 | 41 | 5/15/2021 |
ID002 | 40 | 6/15/2021 |
ID002 | 32 | 7/15/2021 |
ID003 | 50 | 2/15/2021 |
ID003 | 43 | 3/15/2021 |
ID003 | 44 | 4/15/2021 |
ID003 | 40 | 5/15/2021 |
ID003 | 39 | 6/15/2021 |
ID003 | 41 | 7/15/2021 |
ID004 | 40 | 3/15/2021 |
ID004 | 34 | 4/15/2021 |
ID004 | 28 | 5/15/2021 |
ID005 | 41 | 3/15/2021 |
ID005 | 40 | 4/15/2021 |
ID005 | 40 | 5/15/2021 |
ID005 | 40 | 6/15/2021 |
ID005 | 44 | 7/15/2021 |
ID006 | 40 | 3/15/2021 |
ID006 | 48 | 4/15/2021 |
ID006 | 32 | 5/15/2021 |
ID006 | 36 | 6/15/2021 |
ID006 | 44 | 7/15/2021 |
ID007 | 40 | 4/15/2021 |
ID007 | 40 | 5/15/2021 |
ID007 | 39 | 6/15/2021 |
ID007 | 41 | 7/15/2021 |
ID008 | 45 | 4/15/2021 |
ID008 | 44 | 5/15/2021 |
ID008 | 46 | 6/15/2021 |
ID008 | 42 | 7/15/2021 |
ID009 | 47 | 5/15/2021 |
ID009 | 40 | 6/15/2021 |
ID009 | 38 | 7/15/2021 |
ID010 | 40 | 5/15/2021 |
ID010 | 40 | 6/15/2021 |
ID010 | 40 | 7/15/2021 |
Relationships:
Employee 1 -> * Employees Time on EmployeeID
Dates 1 <> * Employees Time on Date and PayPeriod
DAX Measures:
Report Page with Current Results and Desired Results
I would like to get an average time across a certain range of Pay Periods. If someone has not been hired yet, I do not want to include them in the average.
I hope this helps,
Thanks!
@cmaloyb , do you need active employees, then you need hire and termination dates
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |