Forum Discussion

sasi_baddu's avatar
sasi_baddu
Regular Visitor
1 year ago
Solved

Average Headcount

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 attr...
Need Help
  • sasi_baddu's avatar
    sasi_baddu
    1 year ago

    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's avatar
grazitti_sapna
Super User
1 year ago

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's avatar
sasi_baddu
Regular Visitor
1 year ago

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.