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 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

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
    )


3 Replies

  • 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.

       

  • sasi_baddu's avatar
    sasi_baddu
    Regular Visitor
    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
    )