Average Headcount Calculation

[Anonymous]

I need to calculate average headcount 

Will need help in arrving at below number in power bi. My excel look this

Month	Closing Headcount	Avergae HC - Average function
Oct	3585	3585
Nov	3588	3586.5
Dec	3595	3589.333
Jan	3601	3592.25
Feb	3581	3590
Mar	3605	3592.5
Apr	3613	3595.429
May	3616	3598
Jun	3672	3606.222
Jul	3723	3617.9
Aug	3749	3629.818
Sep	3777	3642.083

[Ashish_Mathur]

Hi,

Does the Financial Year run from October to September?  Also, share some data to work with (in a format that can be pasted in an MS Excel file.) or share the download link of your PBI file.

[Anonymous]

Yes we have fiscal year concept.

The solution worked which was provided on trail message.

Thanks!

[ryan_mayu · Accepted solution]

@Anonymous 

1. you need to have a date column or year month column

2. you can create a column

Column = AVERAGEX(FILTER('Table','Table'[year month]<=EARLIER('Table'[year month])),'Table'[Closing Headcount])

or a measure

Measure = AVERAGEX(FILTER(all('Table'),'Table'[year month]<=max('Table'[year month])),'Table'[Closing Headcount])

pls see the attachment below

[Anonymous]

Thanks , this worked

[ryan_mayu]

you are welcome

[Shaurya]

Hi @Anonymous,

Use the following formula:

Average Headcount = SUMX('Table',IF('Table'[Month]<=EARLIER('Table'[Month]),'Table'[Closing],0)) / SUMX('Table',IF('Table'[Month]<=EARLIER('Table'[Month]),1,0))

Mark this post as a solution if that works for you!
Consider taking a look at my blog: How to Export Telemetry Data from Azure IoT Central into Power BI