CODEWITHSUNDEEP
excelpivot-tablepowerquery
snalmznh
snalmznh

Reputation: 59

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!

Upvotes: 1

Views: 716

Answers (1)

DjC
DjC

Reputation: 1272

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

Upvotes: 1

Related Questions