How to Calculate FTE's! Correct monthly, but wrong total!

[Anonymous]

Hi!

I'm struggeling with a FTE (Full Time Employement) calculation. I get the correct monthly number, but the total is totally wrong. 

Here's my measure: 

FTE =

VAR maxdate =

MAX(Calendar_Live[Date])

VAR mindate =

MIN(Calendar_Live[Date])

Return

CALCULATE(SUMX(HR_data, HR_data[Employment_rate]),

FILTER(HR_data, HR_data[Hiredate]<=mindate &&(OR(HR_data[Enddate]>=maxdate,HR_data[Enddate]=0)) && HR_data[Employment_rate.]>0))

This messure will return this (correct by month, but what is going on with the Total?)

The total should be the average of the year.

If anyone got a solution to this, please share ! 🙂

[Anonymous]

Hi, thanks!

I tried to use this formula, but the first line :

SUMMARIZE ( HR_data, Calendar_Live[Year], Calendar_Live[Month] )

does not work, because the "Calendar_Live" is a different table than HR_data

[tamerj1]

@Anonymous

Ok, I got you. Please try

FTE =
SUMX (
    SUMMARIZE ( Calendar_Live, Calendar_Live[Year], Calendar_Live[Month] ),
    CALCULATE (
        VAR maxdate =
            MAX ( Calendar_Live[Date] )
        VAR mindate =
            MIN ( Calendar_Live[Date] )
        RETURN
            CALCULATE (
                SUMX ( HR_data, HR_data[Employment_rate] ),
                FILTER (
                    HR_data,
                    HR_data[Hiredate] <= mindate
                        && (
                            OR ( HR_data[Enddate] >= maxdate, HR_data[Enddate] = 0 )
                        )
                        && HR_data[Employment_rate.] > 0
                )
            )
    )
)

[Anonymous]

Hi, thanks again!

Now we are getting super close! :

The only thing now is that the total is summing each month, but the total in this case should represent the yearly average FTE. Wanted result should be like this:

Is this possible? 🙂

[Anonymous · Accepted solution]

Hi again! 

I think i solved this! I just changed the first "SUMX" to "AVERAGEX" like this:

FTE =
AVERAGEX (
    SUMMARIZE ( Calendar_Live, Calendar_Live[Year], Calendar_Live[Month] ),
    CALCULATE (
        VAR maxdate =
            MAX ( Calendar_Live[Date] )
        VAR mindate =
            MIN ( Calendar_Live[Date] )
        RETURN
            CALCULATE (
                SUMX ( HR_data, HR_data[Employment_rate] ),
                FILTER (
                    HR_data,
                    HR_data[Hiredate] <= mindate
                        && (
                            OR ( HR_data[Enddate] >= maxdate, HR_data[Enddate] = 0 )
                        )
                        && HR_data[Employment_rate.] > 0
                )
            )
    )

And the result now looks like this (just as I wanted): 

Unless there is something that I've missed here, thank you so much for your help! 🙂

[tamerj1]

Hi @Anonymous 
Please try

FTE =
SUMX (
    SUMMARIZE ( HR_data, Calendar_Live[Year], Calendar_Live[Month] ),
    CALCULATE (
        VAR maxdate =
            MAX ( Calendar_Live[Date] )
        VAR mindate =
            MIN ( Calendar_Live[Date] )
        RETURN
            CALCULATE (
                SUMX ( HR_data, HR_data[Employment_rate] ),
                FILTER (
                    HR_data,
                    HR_data[Hiredate] <= mindate
                        && (
                            OR ( HR_data[Enddate] >= maxdate, HR_data[Enddate] = 0 )
                        )
                        && HR_data[Employment_rate.] > 0
                )
            )
    )
)