locked
Fiscal Year to Date RRS feed

  • Question

  •  have the below table in powerpivot of excel

    E.No    E.Name    DOJ
    1    AA    1-Jan-11
    2    AB    15-Jan-11
    3    AC    5-Feb-11
    4    AD    3-Mar-11
    5    AE    23-Apr-11
    6    AF    3-May-11
    7    AG    15-May-11
    8    AH    21-May-11
    9    AI    24-May-11
    10    AJ    12-Jun-11
    11    AK    24-Jun-11
    12    AL    5-Jul-11
    13    AM    10-Jul-11
    14    AN    25-Jul-11
    15    AO    18-Aug-11
    16    AP    16-Sep-11
    17    AQ    10-Oct-11
    18    AR    21-Oct-11
    19    AS    11-Nov-11
    20    AT    25-Nov-11
    21    AU    8-Dec-11
    22    AV    15-Dec-11
    23    AW    27-Dec-11
    24    AX    1-Jan-12
    25    AY    15-Jan-12
    26    AZ    5-Feb-12
    27    BA    3-Mar-12
    28    BB    23-Apr-12
    29    BC    3-May-12
    30    BD    15-May-12
    31    BE    21-May-12
    32    BF    24-May-12
    33    BG    12-Jun-12
    34    BH    24-Jun-12
    35    BI    25-Jun-12
    36    BJ    10-Jul-12
    37    BK    25-Jul-12
    38    BL    18-Aug-12
    39    BM    1-Sep-12




    I want the out put to show the total number of employees cummulative count fiscal year wise.
    FY:11-12
            
    Month    Actual_Count    Cummulative_count
    April        1    1
    May        4    5
    June        2    7
    July        3    10
    August        1    11
    September    1    12
    October        2    14
    November    2    16
    December    3    19
    January        2    21
    February    1    22
    March        1    23


    FY:12-13        

    Month    Actual_Count    Cummulative_count
    April        1    1
    May        4    5
    June        3    8
    July        2    10
    August        1    11
    September    1    12
    October            12
    November        12
    December        12
    January            12
    February        12
    March            12


    Request you all to help me to write the formula for the required output.
    • Edited by mkkrkiran Monday, September 17, 2012 7:07 PM
    Monday, September 17, 2012 7:04 PM

Answers

  • I was able to create the output you asked for based on your data plus a date table.  Here are the steps to recreate it.

    1. Create a date table with all dates from before your min date to somewhere after your max date.  This date table should have your fiscal year defined for each row.  Also need the Month number, month name and the fiscal month number (March=12, April=1, etc.).

    2. create a relationship from DOJ in the Employee table to date in the Date table.

    3. calculated columns:
    I created the fiscal month number as a calculated column like this. 

    FiscalMonthNumber:=IF('Date'[Month] <= 3,'Date'[Month] + 9,'Date'[Month] - 3)

    4. calculated measures:

    CountEmployees:=COUNTROWS(Employee)
    
    CountEmployees_YTD:=CALCULATE([CountEmployees],DATESYTD('Date'[date],"3/31"))

    5. Set your MonthName field to sort according to FiscalMonthNumber

    6. Build the pivot like this.  I pasted your desired output next to it to check.

    Hope that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Thursday, September 20, 2012 4:07 AM
    Answerer