Average employee headcount per year including average of part year

[Bfaws]

Hello Power BI Community,

I was hoping somebody could help.  I`m looking for a measure that would show average employee headcount per year, including, part years.  I extract a report on a monthly basis on 1st of each month and this is included in the table as "Report Date".

What I would like to show is what was the average for say 2019 which would be the count of employees "RESID" for each month divided by 12 - this would work well for each completed year.  I would like to extend this measure to show the average count for the current year which is a part year - so if updated the data in March the 2020 year average would show as the count of "RESID" for each month in the year divided by 3, April would show as the count of "RESID" for each month in the year divided by 4, and so on.

Please see link to data below.

Click here for .pbix file 

Many thanks in advance,

Brendan 

[amitchandak]

@Bfaws , did not see the pbix yet. see if this one can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Fowmy · Accepted solution]

@Bfaws 

Use this measure for the correct average:

Yearly Avg Employees Count = 

AVERAGEX(
    SUMMARIZE(
        ALLEXCEPT('Resource reports - year average',Rolling_Calendar_Lookup[Year]),
        Rolling_Calendar_Lookup[Fiscal Year],
        Rolling_Calendar_Lookup[Month Name]
    ),
    [Employee_count]
)

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS 👍 to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[Bfaws]

@Fowmy

Many thanks - that has done the trick.

Much appreciated.

Brendan

[Fowmy]

@Bfaws 

Great 👍 

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS ? to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel