Average Headcount

[sasi_baddu]

Hi Everyone and
@Ashish_Mathur @amitchandak 

I Have months from April to March as I follow India financial year and opening and closing headcount as follows along with starts, exits, monthly attrition, YTD attrition. 
As an example: 
For April YTD = Exits in April / Average (opening HC of April, closing HC of April)
For May YTD = Sum of Exits in April & May / Average(closing HC of April, closing HC of May)
For June YTD = Sum of Exits in April, May & June / Average(closing HC of April, closing HC of May, closing HC of June)
and so on till year end

The average headcount that i manually calculated is highlighted in Amber. 

How to achieve this in Power Bi using a measure where the average headcount against the months 

Below is the .pbix file link and my date relation in Power BI.
https://drive.google.com/file/d/1UrwjF6EP28ZvrKUWKyBix-YisBTR6879/view?usp=drive_link

With the help of this community, I came so far in building the attached dashboard. however, i am not able to figure out how can i get the average headcount measure

I have come across Solved: Re: Average Headcount Calculation - Microsoft Fabric Community but not able to use it in the way I want it. 
Can someone help on this

Regards

Sasi

[sasi_baddu · Accepted solution]

I was able to crack it. Thank for everyone who looked at it.

new average =

VAR CurrentMonth = MAX('Date'[Date])

VAR OpeningHC = [open hc]

VAR ClosingHC = [close hc]

VAR CumulativeClosingHC =

    CALCULATE(

        SUMX(

            VALUES('Date'[Date]),

            [close hc]

        ),

        FILTER(

            ALL('Date'),

            'Date'[Date] <= CurrentMonth

        )

    )

VAR MonthNumber = MONTH(CurrentMonth)

RETURN

SWITCH(

    TRUE(),

   MonthNumber = 4, (OpeningHC + ClosingHC) / 2,

    MonthNumber = 5, (CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 2,

    MonthNumber = 6, (CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 3,

    MonthNumber = 7, (CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 4,

    MonthNumber = 8, (CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 5,

    MonthNumber = 9, (CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 6,

    MonthNumber = 10, (CALCULATE([close hc], DATEADD('Date'[Date], -6, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 7,

    MonthNumber = 11, (CALCULATE([close hc], DATEADD('Date'[Date], -7, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -6, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 8,

    MonthNumber = 12, (CALCULATE([close hc], DATEADD('Date'[Date], -8, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -7, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -6, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 9,

    MonthNumber = 1, (CALCULATE([close hc], DATEADD('Date'[Date], -9, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -8, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -7, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -6, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 10,

    MonthNumber = 2, (CALCULATE([close hc], DATEADD('Date'[Date], -10, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -9, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -8, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -7, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -6, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 11,

    MonthNumber = 3, (CALCULATE([close hc], DATEADD('Date'[Date], -11, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -10, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -9, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -8, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -7, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -6, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 12,

    (CumulativeClosingHC + OpeningHC) / MonthNumber

)

[grazitti_sapna]

Hi @sasi_baddu,

You need a running sum of exits field and a dynamic average of closing headcounts up to the current month.

We can create separate measures for this,

Measure 1 :

YTD_Exits =
CALCULATE(
SUM(YourTable[Exits]),
DATESYTD(YourTable[Date], "03/31")  // for April-March Financial Year
)

Measure 2 :

YTD Avg Closing HC =
VAR SelectedMonth = MAX(YourTable[Date])
VAR FilteredHC =
FILTER(
YourTable,
YourTable[Date] <= SelectedMonth
)
RETURN
AVERAGEX(FilteredHC, YourTable[Closing HC])

Measure 3: Let's put these together to get the required values

YTD Attrition =
DIVIDE(
[YTD Exits],
[YTD Avg Closing HC],
BLANK()
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[sasi_baddu]

Hi @grazitti_sapna ,

I do not have closing headcount in a table rather a measure. 
Closing Headcount = Calculate([current employees],LASTDATE('Date'[Date])

Also, in measure 2, YourTable[Date] <= SelectedMonth. YourTable[Date], do i need to select the start date in my data table ? or should i select this from Date table as I am not able to add anything apart from the start date from my data table.