Forum Discussion

dzaba's avatar
dzaba
Frequent Visitor
5 years ago

Employee Capacity, Turnover vs. FTEs

Hello Folks,

 

I prepared some pbi report regarding to employee count and the turnover of them. 

 

The Next step for me is to recalculate it from Heads to FTE. And here my problems started 🙂

 

So what I need to receive is the FTE count and retention divided by dept (it will be a slicer).

Here is also pbi test file, with what i did so far. 

 

https://1drv.ms/u/s!AmHaMHGdkcnBaSnxy5AibD66E7Q?e=aWH8Hs 

 

 

 

Need Help

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    Have you considered using the "What if?" Parameter option in Power BI?

     

    • dzaba's avatar
      dzaba
      Frequent Visitor
      5 years ago

      No it will not help in this case I think. thanks

    • DataInsights's avatar
      DataInsights
      Icon for Super User rankSuper User
      5 years ago

      dzaba,

       

      First, create a relationship between Sheet1 and Sheet3. I had to remove a duplicate UserID from Sheet1 in order to create the relationship.

       

       

      Second, create these measures:

       

      Sum FTE = SUM ( Sheet3[FTE] )

FTE Count = 
VAR selectedDate =
    MAX ( 'Date'[Date] )
VAR vResult =
    SUMX (
        'Sheet1',
        VAR employeeStartDate = [EmploymentStartDate]
        VAR employeeEndDate = [EndDate]
        RETURN
            IF (
                employeeStartDate <= selectedDate
                    && OR ( employeeEndDate >= selectedDate, employeeEndDate = BLANK () ),
                [Sum FTE]
            )
    )
RETURN
    vResult

       

      Set the format for [FTE Count] to Decimal number (and the desired number of decimals).