Add a Fiscal Month, Quarter or Year Column in Power Query | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★

★ Check out the blog post★

★ About this video ★
Over the last few months, I've been asked several times how to add a fiscal year, fiscal quarter, or fiscal month column in Power Query. So I decided to note down the method so I don't have to keep reminding myself how I did it last time.

The good news is that it's reasonably straightforward. So let's see what we need to do.

CONTENTS
0:00 Introduction
0:31 Example Data
1:12 Fiscal Month
3:13 Fiscal Year
4:48 Fiscal Quarter
6:11 Conclusion

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[IvanCortinas_ES]

Excellent proposal. Highly demanded by companies. Thank you very much, Mark.

[ingridgwladys]

Wooow BRILLIANT ! Awesome delivery in 6mins. Thank you soooo much

[Maheshpanduranga]

Hi Mark, It was really an excellent way to arrive at Fiscal year and Month. The last one to calculate Fiscal quarter was awesome and brilliant 😊

[GoodlyChandeep]

Hey Mark, this is how I do it.
Fiscal Month = Date.Month(Date.AddMonths([Date], -3))

[MaureenPesch]

Thank you- somuch easier than what I’ve been doing!

[VuNguyenAnthony]

very helpful. Thanks so much Mark for sharing your knowledge

[vsrinivasan]

Thanks, Thanks to Mr.Chandeep through whom we got to know you.

[VPudman]

Thank you so much for sharing your knowledge

[nicolemullis]

Do you have a clever way to add fiscal week number with the same example of April 1 start?

[RameezMannil]

Hi, I need the last Thursday of the month to be considered as month cut-off. The remaining days of that month goes to next month. Any solutions please..

Apparently I live in the GCC

[bengiblett]

Hey Mark, Very useful, thanks. So if I did need to modify it slightly such that the calendar month and fiscal month don't map equally i.e. our fiscal month or "period end" as we call it is usually a few days before or after the actual calendar month end, do you have a clever solution for that scenario? Have you already done a video on it ? My current "clunky" solution is a big long lookup table but I'm sure there are better ways. I would appreciate any suggestions. Thx Ben

[nothingcanstopyou]

Lots of love from Bharat that is India

[kebincui]

Hi Mark. At 4:27 for Fiscal Year calculation, I feel if Date.Month(Date)<=3, the fiscal year should be Date.Year(Date)-1, else should be Date.Year(Date)

[lizzieleman]

How could I get fiscal year to show as 2022-23 for example?

[rodneyplunkett]

Interesting, but how do you determine the fiscal month in a 4-4-5 fiscal calendar?

[jerrydellasala]

I hate all those #"" in the M Code and typically rename every step removing spaces at least, and prefer them to be more descriptive. Also, rather than having to adjust the FY End month in multiple places if I need this query again, rather than missing an entry I prefer using a variable - just as one would use the value in a cell rather than hard coding a value in a Worksheet. Towards those ends I adjusted the M Code manually adding the line of code for the variable in the Advanced Editor immediately after the Source line. Doing it that way bypasses the PQ Editor getting confused over step sequence (although once there it can be changed without using the Advanced Editor):

let
Source = Excel.CurrentWorkbook(){[Name="Dates"]}[Content],
FYEndMonth = 3,
ChangedDateType = Table.TransformColumnTypes(Source, {{"Date", type date}}),
AddedFiscalMonth = Table.AddColumn(ChangedType, "Fiscal Month", each if Date.Month([Date]) <= FYEndMonth then Date.Month([Date]) + 12 - FYEndMonth else Date.Month([Date]) - FYEndMonth),
AddedFiscalYear = Table.AddColumn(AddedFiscalMonth, "Fiscal Year", each if Date.Month([Date]) <= FYEndMonth then Date.Year([Date]) else Date.Year([Date]) + 1),
AddedFiscalQuarter = Table.AddColumn(AddedFiscalYear, "Fiscal Quarter", each Number.RoundUp([Fiscal Month]/3, 0)),
ChangedNewColTypes = Table.TransformColumnTypes(AddedFiscalQuarter, {{"Date", type date}, {"Fiscal Month", Int64.Type}, {"Fiscal Year", Int64.Type}, {"Fiscal Quarter", Int64.Type}})
in
ChangedNewColTypes

Note - while I agree fewer steps are usually better, when adding a custom column, there's nothing wrong with adding the data type to the end of the M Code, however if the step is edited, the type will have to be added to that line again - AFTER clicking OK in the Add Custom dialog window. That's why I didn't bother to change the three new column data types until the new columns were all created a lot easier. Rather than manually adding the data type to 1-3 steps, all 3 columns are defined by selecting one column, hit [Ctrl]A, click Transform (tab), [Detect Data Type]. No typing involved!
Just my $0.02.