Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Calculate Employee Count

    Posted 16 days ago
      |   view attached
    I have this data (attached) of employee joining and leaving years. Attached picture is what i want to create:

    Can you please help me calculate the total employees (cumulative) in each year:


    ------------------------------
    Gulfam Ahmad
    Head of Analytics
    ------------------------------

    Attachment(s)



  • 2.  RE: Calculate Employee Count

    Bronze Contributor
    Posted 14 days ago
    First create a list of the years from start to end:

    Years = GENERATESERIES(MIN('SourceData'[Joining_Year]),Max('SourceData'[Leaving_year]))

    Then add a measure to this to add up all the starters before or equal to this year, and all the leavers before or after this year

    (or leavers before this year ?

    HeadCount =
    VAR CurrentYear = SELECTEDVALUE('Years'[Value])
    RETURN
    CALCULATE(COUNT(SourceData[Joining_Year]),ALL(SourceData),'SourceData'[Joining_Year]<= CurrentYear) - CALCULATE(COUNT(SourceData[Leaving_Year]),ALL(SourceData),'SourceData'[Leaving_Year]<= CurrentYear)


    ------------------------------
    James Watts
    Business Intelligence Analyst
    ------------------------------



  • 3.  RE: Calculate Employee Count

    Posted 14 days ago
      |   view attached

    Hi Gulfam,

    You can create a year table and connect or link your joining year as active link and leaving year as inactive to the year table, then create a formula:

    CumCount =

    var J = CALCULATE(COUNT(Emp[Unique_Key]),'Year'[Value]<=max('Year'[Value]))
    var YV = VALUES('Year'[Value])
    var L =
    CALCULATE(COUNT(Emp[Leaving_year]),
        USERELATIONSHIP(Emp[Leaving_year],'Year'[Value])
        ,'Year'[Value]<=yv
        )

    Var R = j-l

    Return

    R


    Hope this help.

    Regards,
    Ardie



    ------------------------------
    Rd 3nidad
    Senior Finance Analyst
    ------------------------------

    Attachment(s)

    pbix
    EmployeeCount.pbix   58 KB 1 version


  • 4.  RE: Calculate Employee Count

    Posted 11 days ago
      |   view attached
    Hi Gulfam, If you encounter an error, you may use the revised file attached.

    ------------------------------
    Rd 3nidad
    Senior Finance Analyst
    ------------------------------

    Attachment(s)

    pbix
    EmployeeCount.pbix   58 KB 1 version


  • 5.  RE: Calculate Employee Count

    Posted 13 days ago
      |   view attached
    Veja se o anexo ajuda

    ------------------------------
    Vilmar Santos
    ------------------------------

    Attachment(s)