Issues with Head Count and Full time Equivalent (FTE) calculation

DAX DAX Calculations
1

Hello,

i need to calculate Head Count and Full time Equivalent in one model. Details are in the image.

My main questions are also in the image. For the 2nd question, a slider would also be great.

In order to get this:

1
1846×457 11.5 KB

i created a sample factual with dimension client and date. Contents of factual:

2

The next 3 measures are related to Head count:

# Resources ALL = 
VAR MinDate = MIN ( 'Date'[Date] )
VAR MaxDate = MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (                                 -- If any order can have several rows
        DISTINCTCOUNT ( factFce[id] ), -- use DISTINCTCOUNT instead of COUNTROWS 
        factFce[entry date] <= MaxDate,
        factFce[exit date]> MinDate,
        REMOVEFILTERS ( 'Date' )
    )
RETURN
    Result

# Resources AVG = 
AVERAGEX (
    'Date',
    [# Resources ALL]
)

# Resources EOP = 
CALCULATE (
    [# Resources ALL],
    LASTDATE ( 'Date'[Date] ) 
)

# Resources AVG and #Resources EOP worked great, mainly the AVG one does the average without any issues.

But when i try to calculate at FTE level (HC * Activity Rate * day of leaving/nr days in the month), it didn’t work.
So #Resources FTE ALL is (at least for now) equal to #Resources ALL, and the following 2 are calculated columns because i needed to place them on columns and also to try to get the right context.

# Resources FTE ALL =  -- equal to #Resources ALL
VAR MinDate = MIN ( 'Date'[Date] )
VAR MaxDate = MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (                                 -- If any order can have several rows
        DISTINCTCOUNT ( factFce[id]  ), -- use DISTINCTCOUNT instead of COUNTROWS 
        factFce[entry date] <= MaxDate,
        factFce[exit date]> MinDate,
        REMOVEFILTERS ( 'Date' )
    )
RETURN
    Result

# Resources FTE AVG = [# Resources AVG] * factFce[activity rate]

# Resources FTE EOP = [# Resources EOP] * factFce[activity rate] * IF(ISBLANK(factFce[exit date]),1, DAY(factFce[exit date])/ DAY(ENDOFMONTH(factFce[exit date])))

3
So, thought HC works great, but on matrix FTE where i use # Resources FTE EOP results are bad:

  1. March doesn’t appear
  2. values are wrong - for me it makes sense to use the EOP one here because if i use the average i don’t know to what point i can control the calculation, that is, if a worker leaves on 15/3, working 14 days, if the final average with AVG is ok…
  3. in that sense, it seemed to me that i only needed to multiply Activity Rate * day of leaving/nr days in the month to HC in the final AVG calculated column
  4. if i had another dimensions, does the calculation work ok?

Thanks for any help

2

Welcome to the Enterprise DNA Forum @CC_IPCA. We’re happy to have you in the community. To get started, I highly suggest you check these guides that Sam created for us to build a more collaborative environment. How To Use The Enterprise DNA Support Forum

3

Thanks EnterpriseDNA. Can help me with my issue?

4

Hi @CC_IPCA! We noticed that your inquiry was left unsolved for quite some time now. We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!