Forum Discussion

djking01's avatar
djking01
New Member
3 years ago
Solved

Calculating Average Monthly Rate

Hello, 

I currently have 3 calculations that are related to what I am trying to accomplish:
1: This calculates the count of Volterm = 1

VolTerm =
Calculate(
    COUNTX(FILTER('HR Data','HR Data'[Termination Date]<=MAX('Calendar'[Date])
    && 'HR Data'[Termination Date]>=MIN('Calendar'[Date])
    && 'HR Data'[VolTerm]>0),'HR Data'[Employee Number])
)
2: This calculates Headcount
Headcount =
Calculate(
    COUNTX(FILTER('HR Data','HR Data'[Last Hire Date]<=MAX('Calendar'[Date])
    && (ISBLANK('HR Data'[Termination Date])
    || 'HR Data'[Termination Date]>MAX('Calendar'[Date]))),'HR Data'[Employee Number]))

3: This calculates resignation rate
Resignation Rate by Month = [VolTerm]/[Headcount]

What I want to do is to make a calculation that returns the average monthly resignation rate for a given month/year so that I can display it on a line graph. See example below:

Jan 23 Rate: 1%                       Jan 23 Avg Rate 1%
Feb 23 Rate: 2%                      Feb 23 Avg Rate 1.5%
Mar 23 Rate: 1.25%                 Mar 23 Avg Rate 1.417%

Thanks!
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    djking01 , Try measures like this with date table and active inactive joins

     

    Hired Employee = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[Start Date],'Date'[Date]) )

     

    Terminated Employees = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[End Date],'Date'[Date]),not(ISBLANK(Employee[End Date])))

     

    Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

     

    Cumm Terminated = calculate([Terminated Employees], filter(all('Date') , 'Date'[Date] <=max('Date'[Date])))

     

    Cumm Hired = calculate([Hired Employee], filter(all('Date') , 'Date'[Date] <=max('Date'[Date])))

     

    for Avg refer

    Average of Rolling, Average of Snapshots: https://youtu.be/_pZRdLAJxxA

    Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
    https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    djking01 , Try measures like this with date table and active inactive joins

     

    Hired Employee = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[Start Date],'Date'[Date]) )

     

    Terminated Employees = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[End Date],'Date'[Date]),not(ISBLANK(Employee[End Date])))

     

    Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

     

    Cumm Terminated = calculate([Terminated Employees], filter(all('Date') , 'Date'[Date] <=max('Date'[Date])))

     

    Cumm Hired = calculate([Hired Employee], filter(all('Date') , 'Date'[Date] <=max('Date'[Date])))

     

    for Avg refer

    Average of Rolling, Average of Snapshots: https://youtu.be/_pZRdLAJxxA

    Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
    https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970