Calculate montly average FTE based on employee contract start and end date.

[BobKoenen]

Hi All,

I want to calculate the monthly FTE a department has my page contains a Slicer for month and multiple months or a whole year can be selected. My data looks like this

Dep	Person	startdate	Enddate	FTE
Finance	Joe	1-1-2021	31-5-2021	1
FInance	Joe	1-6-2021		0,8
Finance	Jane	1-1-2021		0.6
Finance	Jill	1-4-2021	31-12-2021	1

If i would slice the first 6 months of the year 2021 the result should be as follows
Joe (5*1 +1*0,8)/6 = 0.96
Jane (6*0,6)/ 6= 0,6

Jill (3*1)/6=0.5

And  i want to the total of the Finance department to show to total of the average FTE per employee in this period 0.96+0.6+0.5 = 2.06


How can this be done

[v-easonf-msft · Accepted solution]

Hi, @BobKoenen 

You can consider transforming your original table into the structure shown in the figure below:

PQ:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvMS8xLTlXSUfLKB5GGuoa6RgZGhkCmsaGuKYxtqBSrA1Tsia7YDKZAAYgN9CwgyhBmJuahGQpRZ4auLjMnB6zOBMlyQyMk22MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dep = _t, Person = _t, startdate = _t, Enddate = _t, FTE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dep", type text}, {"Person", type text}, {"startdate", type date}, {"Enddate", type date}, {"FTE", type number}}, "en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each Number.RoundDown(Duration.Days(([Enddate] - [startdate]) / 30),0)),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Rows", each if [Month] <> null then [Month] else 12),
    #"Added Custom2" = Table.AddColumn(#"Added Conditional Column", "List", each {Number.From(1)..Number.From([Rows])}),
    #"Expanded List" = Table.ExpandListColumn(#"Added Custom2", "List"),
    #"Added Custom1" = Table.AddColumn(#"Expanded List", "date_month", each Date.AddMonths([startdate],[List]-1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"startdate", "Enddate", "Month", "Rows", "List"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Dep", "Person", "date_month", "FTE"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Reordered Columns", {{"date_month", type date}}, "en-GB")
in
    #"Changed Type with Locale"

 Add measure as below:

Result = 
VAR month_count =
    IF (
        ISFILTERED ( 'Calendar'[Date].[Month] ),
        ROUND ( COUNTROWS ( 'Calendar' ) / 30, 0 ),
        12
    )
RETURN
    ROUNDDOWN ( SUM ( 'Table'[FTE] ) / month_count, 2 )

Please check my sample file for more details.

You can also refer to this related thread.

Split-period-data-into-months-and-calculate-fraction-of-yearly/td-p/254895 

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