Calculate Custom Fiscal Year and Quarter in Power Query

If you have been working in power query for a while you know that Power Query allows you to extract Year and Quarter from a date but what if you want get the year or quarter as per your fiscal calendar

In this post I am going to share with you that how can you use custom functions to calculate fiscal years and quarters from dates in Power Query

 

Want to grab the Video first?

 

Assume these dates..

Since I work most of the times with Indian Fiscal calendar, I am going to be discussing that how can you get Indian Fiscal Year and Quarter from the dates. But hey, I have built the provision for you to customize this to your own fiscal calendar.

Load these Dates to Power Query

1. Click on the table
2. Then in the Data Tab >> From Table/Range
3. The data lands in Power Query
4. Let the dates be in Power Query for a while, we’ll come back to them

 

Creating 2 Custom Functions

Now we’ll need 2 custom functions to calculate fiscal year and fiscal quarter in Power Query. I am sharing the M Code below, unless you really want to rip it apart to see what I have done, you can just use them without understand their construct

(DateValue as date, MonthEnding as number) =>
let
    CY = Text.End ( Text.From ( Date.Year ( DateValue ) ) , 2 ),
    NY = Text.End ( Text.From ( Date.Year ( DateValue ) + 1 ) , 2 ),
    PY = Text.End ( Text.From ( Date.Year ( DateValue ) - 1 ) , 2 ),
    MonthCheck = Date.Month ( DateValue ) > MonthEnding,
    FiscalYear = 
        if
            MonthCheck
        then 
            CY & '-' & NY  
        else
            PY & '-' & CY 
in
    FiscalYear

And the second custom function for fiscal quarter

(DateValue as date, MonthEnding as number) =>
let
    AdjustedMonth = 'Q' & Text.From ( Number.RoundUp ( Date.Month ( Date.AddMonths ( DateValue, - MonthEnding ) ) / 3 ) )
in
    AdjustedMonth

 

Using these Functions

To be able to use them we’ll need to create a blank query

1. The Power Query Editor Window go to Home Tab
2. On the Extreme Right you’ll have a New Source Drop Down >> Other Sources >> Blank Query

Once you get a blank query go the View Tab >> Advanced Editor >> Delete everything and paste the first code there. Also rename the Query to something more meaningful like “Get FY”

You would do the same for second custom function. In a blank query paste the second M code and rename it to “Get Fiscal Qtr”. Now that we have these functions created, let’s see how can we use them

 

Applying Custom Functions to Dates

In our Query for Dates ensure that the dates are in Date Format (and not datetime or any other)

1. From the Add Columns Tab >> Custom Column
2. Write the function = #”Get FY” ( [Dates] , 3 )
3. Just to help you understand, our function takes 2 arguments
   • The Date Column of your Data (in our case [Dates])
   • Fiscal Year Ending Month number. I wrote 3 because in India the financial year ends in March (I am sure you get the idea..)
4. Hit Ok! that’s it, we are done!

 

For finding the Fiscal Quarter I am going to write another Custom Column using the second function

= #”Get Fiscal Qtr” ( [Dates] , 3 ) which accepts the same two arguments : Dates and Fiscal Year Month Ending

 

Everyone likes to “Close and Load” the data after a successful querython! (that was made up, but it felt nice). Let me know if this solution works for you, although I have already written about how to create financial year calendar in Power BI using DAX but I thought a lot of folks also want it in Power Query, so there you go..

 

 

More Power Query Good(ly)ness!

1. Calculate Age in Years and Months
2. Vlookup in Power Query
3. Power Query – Tips and Tricks (pdf tip card)
4. Filter Data with Multiple Conditions
5. SUMIF in Power Query
6. Combine Data from Multiple Sheets in a Single Sheet
7. Combine Data from Multiple Excel Files in a Excel File