Search code examples
excelpivot-tablepowerquery

Is there a simple way to display months in a pivot table as per fiscal year?


I have a dataset with dates for the past 4 years. I have an appointment column (appt date) with format mm/dd/yyyy and customer column (cust ID)

I would like to create pivot tables displaying the number of customers in a fiscal year, which runs April 1-Mar 31. Therefore, I would like the rows to look like, for example:

2022: Apr May Jun July Aug Sep Oct Nov Dec 2023: Jan Feb Mar

Is there a way to either a) filter in the pivot table to months in a fiscal year b) a conditional column that could be created in power query to label the appointment date to the corresponding fiscal year

Thank you!


Solution

  • You could try either of the following:

    METHOD 1 - Calculated Columns in the Source Table:

    Insert a new column in the data table for "Fiscal Year", and use the following formula:

    =YEAR([@[appt date]])+(MONTH([@[appt date]])>3)
    

    Insert another new column in the data table for "Fiscal Month", and use the following formula:

    =TEXT(CHOOSE(MONTH([@[appt date]]),10,11,12,1,2,3,4,5,6,7,8,9),"00")&TEXT([@[appt date]]," - mmm")
    

    Then, with your data table selected, go to Insert > Pivot Table.

    add_to_source_table.png

    METHOD 2 - Custom Columns in Power Query:

    In Excel, select your data table, then go to Data > Get & Transform Data > From Table/Range.

    In Power Query Editor, go to Add Column > Custom Column, name it "Fiscal Year" and use the following column formula:

    = Date.Year( Date.AddMonths( [appt date], 9) )
    

    Again, go to Add Column > Custom Column, name it "Fiscal Month" and use the following column formula:

    = Number.ToText( Date.Month( Date.AddMonths( [appt date], - 3 ) ), "00" ) & DateTime.ToText( [appt date], " - MMM" )
    

    Then, go to File > Close & Load To... and select "Pivot Table Report".

    add_with_power_query.png