locked
Monthwise count of Emp RRS feed

  • Question

  • I have the below table in powerpivot of excel

    Emp_no    E_Name    DOJ
    1    AA    1-Jan-2012
    2    AB    15-Jan-2012
    3    AC    5-Feb-2012
    4    AD    3-Mar-2012
    5    AE    23-Apr-2012
    6    AF    3-May-2012
    7    AG    15-May-2012
    8    AH    21-May-2012
    9    AI    24-May-2012
    10    AJ    12-Jun-2012
    11    AK    24-Jun-2012
    12    AL    5-Jul-2012
    13    AM    10-Jul-2012
    14    AN    25-Jul-2012
    15    AO    18-Aug-2012


    I want the out put to show the total number of employees independent of month as below

    Month    total Emp
    May    9
    Jun    11
    Jul    14


    Request you all to help me to write the formula for the required output.
    Tuesday, September 4, 2012 9:09 AM

Answers

  • I created a measure TotalNumOfEmployees :=

    CALCULATE(TOTALYTD(COUNTROWS('Table1'), 'Table1'[DOJ], ALLEXCEPT('Table1', 'Table1'[DOJ])))

    and the result will look like this:


    -- This posting is provided "AS IS" with no warranties, and confers no rights

    • Proposed as answer by ManjunathRV Saturday, September 8, 2012 12:04 PM
    • Marked as answer by Elvis Long Thursday, September 13, 2012 1:11 AM
    Thursday, September 6, 2012 12:23 AM

All replies

  • I created a measure TotalNumOfEmployees :=

    CALCULATE(TOTALYTD(COUNTROWS('Table1'), 'Table1'[DOJ], ALLEXCEPT('Table1', 'Table1'[DOJ])))

    and the result will look like this:


    -- This posting is provided "AS IS" with no warranties, and confers no rights

    • Proposed as answer by ManjunathRV Saturday, September 8, 2012 12:04 PM
    • Marked as answer by Elvis Long Thursday, September 13, 2012 1:11 AM
    Thursday, September 6, 2012 12:23 AM
  • Forgot to mention that I created a calculated column [Month] as below

    =MONTH('Table1'[DOJ])


    -- This posting is provided "AS IS" with no warranties, and confers no rights

    Thursday, September 6, 2012 12:24 AM