Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cmaloyb
Helper II
Helper II

DAX to Include Someone in Average After their Hire Date Using Date Range Filter

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

3 REPLIES 3
v-yiruan-msft
Community Support
Community Support

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

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

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

20211115PowerBIForumEmployeeTable.JPG

 

 

PayPeriod

EmployeeIDHoursQuantityEmployees Time Table
ID001381/15/2021
ID001402/15/2021
ID001403/15/2021
ID001254/15/2021
ID001425/15/2021
ID001406/15/2021
ID001457/15/2021
ID002402/15/2021
ID002413/15/2021
ID002354/15/2021
ID002415/15/2021
ID002406/15/2021
ID002327/15/2021
ID003502/15/2021
ID003433/15/2021
ID003444/15/2021
ID003405/15/2021
ID003396/15/2021
ID003417/15/2021
ID004403/15/2021
ID004344/15/2021
ID004285/15/2021
ID005413/15/2021
ID005404/15/2021
ID005405/15/2021
ID005406/15/2021
ID005447/15/2021
ID006403/15/2021
ID006484/15/2021
ID006325/15/2021
ID006366/15/2021
ID006447/15/2021
ID007404/15/2021
ID007405/15/2021
ID007396/15/2021
ID007417/15/2021
ID008454/15/2021
ID008445/15/2021
ID008466/15/2021
ID008427/15/2021
ID009475/15/2021
ID009406/15/2021
ID009387/15/2021
ID010405/15/2021
ID010406/15/2021
ID010407/15/2021

 

Relationships:

20211115PowerBIForumRelationship.JPG 

 

Employee 1 -> * Employees Time on EmployeeID

Dates 1 <> * Employees Time on Date and PayPeriod

 

DAX Measures:
20211115PowerBIForumSumOfPersonnel.JPG20211115PowerBIForumHRsQuantity.JPG20211115PowerBIForumAverageTimeALL.JPG20211115PowerBIForumPayPeriodCount.JPG20211115PowerBIForumSelectPeriodAvg.JPG

 

Report Page with Current Results and Desired Results

20211115PowerBIForumReport.JPG

 

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!

amitchandak
Super User
Super User

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.