Calculate financial fiscal year and quarter based on some given dates

Is very well known that US financial fiscal year and quarters are not the same as calendar ones. As a result, is pretty much common in US companies to be required to calculate these based on some dates representing accounting information. In general, management needs to present financial reports at the end of each quarter or year.

How is typically solved? In almost 100% of the cases, people are using very long Excel formulas or based on VBA or even calculated SharePoint fields if data is stored into a SharePoint list. I have nothing against, just I do not like to complicate things. There is an easier way to do this with a little help from Power Query as you can create simple custom formulas to extract the information you need.

Let’s take a look on the formula to calculate fiscal quarter.

    fiscalQuarter = (fd as nullable date) =>

     m = Date.Month(Date.From(fd)),    
     fm = if m = 11 or m = 12 or  m = 1 then "Q1" else 
          if m = 2 or m = 3 or m = 4 then "Q2" else 
          if m = 5 or m = 6 or m = 7 then "Q3" else 
          if m = 8 or m = 9 or m = 10 then "Q4" else  ""          
     in fm


I have created a formula which accepts a nullable date as parameter. I get the month number based on it and depending on it I return the quarter name. For 1st, 11th, 12th months the returned results is Q1, for 2nd, 3rd and 4th is Q2, for 5th, 6th and 7th is Q3 and for 8th, 9th and 10th is Q4.

To calculate the fiscal year is even an easier thing to do.

    fiscalYear = (fd as nullable date) =>
    let m = Date.Month(Date.From(fd)), y = Date.Year(Date.From(fd)),
        year = if m = 11 or m = 12 then y + 1 else y
    in year 


I get the year value from a date, which is always a number if provided number is not null, and for 11th and 12th months I increase it with 1 unit.

I found this was easier than classical ways which sometimes generate ugly and long code. After all, who wants to work with very very long formulas in Excel formula bar or with calculated SharePoint columns which doesn’t always work as expected. And as a rule I am driven by in my career: a better solution can be much closer to you than you think. 🙂 Just do more research and you will probably find it.


Create custom formula in Power Query

Power Query is a very powerful extension and requires more attention. So, for today, I have decided how to write and use a custom formula. It will be a simple example to show friendly name of the months based on month’s numbers.

Let’s imagine we have this data.
And now we want to show friendly name of the month for each date in the “Start Date” column. Of course we can use some VBA formulas as well, but let’s use something more modern. Just go to “Power Query” ribbon to create a blank query, which will contain our custom formula.Actually the sequence is: Power Query -> Get External Data -> From Other Sources -> Blank Query. We name the query “ShowMonthName” and in Advanced Editor we enter the formula (to enter in the editor respect the sequence: “Home” ribbon -> “Query” tab -> “Advanced Editor” button).
Custom formula
What we actually did? We create a formula called “ShowMonthName” which can be used in Power Query connection. And I am going to show how to do this.

Place mouse inside used range containing dates and do the following: Power Query -> Excel Data -> From Table. Excel will select by default entire used range, so you probably do not need to adjust it and you can go to the next screen, which is our query editor window. Here you go to Add Column tab and click on Add Custom Column button from General tab. And for the next step you need to name your column and insert our formula in formula text area control. This is straight forward and there no other explanation needed.
Add custom column
If everything is correct, you will see the results immediately in the query editor window and, if there is no error, you can use the connection you have just created, which includes the custom formula.
Formula results
I really encourage you to start using Power Query. It will open a new world for you and you will love using it, because it is a step forward in reporting and business intelligence. Leave behind ugly VBA formulas and start looking for the future.