Financial Year Calculation In Excel and Power BI

If you are someone who works with Fiscal Calendar that does not start with Jan and end at December, you are going to love this post. In this post I’ll teach you how can you calculate Custom Fiscal Year, both in Excel and in Power BI

Now if you do know that you have the option of grouping dates in a Pivot Table but by default the grouping happens as per calendar year not the financial year.

And since I am based in India my examples will be with dates as per Indian Financial Calendar which starts in Apr and ends in Mar. You may modify the formulas to suit your need

 

In case you prefer watching a Video first!

 

Let’s start with this simple Sales Data

Quick Question – Summarize Sales by Indian Financial Year and Quarters.

The Catch – Because the default grouping option in pivot tables will enable calendar year wise grouping, we’ll have to create two additional columns for Fiscal Year and Fiscal Quarter

 

Fiscal Years & Qtrs in India

In-case you are born and brought up with Indian Fiscal Calendar, I don’t need to explain this much. But just in-case you need slight explanatory help, here here are my 2 cents

1. The quarter counter starts from Apr and ends in Mar
   • Q1 Apr – Jun
   • Q2 Jul – Sep
   • Q3 Oct – Dec
   • Q4 Jan – Mar
2. Fiscal year is written combining 2 years
   • Consider 1-Apr-2019 – As soon as the month changes to Apr and beyond, the Fiscal Year is written as FY 19-20
   • The above (FY 19-20) would go on until 31-Mar-2020, post which the Fiscal year would change to FY 20-21

 

Formula for Calculating Fiscal Year

Here is the formula

=IF(MONTH(date)>3,YEAR(date)&"-"&YEAR(date)+1,YEAR(date)-1&"-"&YEAR(date))

How it works – The formula simply checks for the current date’s month against march and adjusts the year as per that!

 

Formula for Calculating Fiscal Quarter

="Q"&CEILING(MONTH(EDATE(date,-3)),3)/3

I explain the logic in-detail in this post

Check out the links, in case you din’t know Ceiling, EDate Functions 🙂 After you are done creating these 2 columns, you should be able to use these in the Pivot Table

 

How to create a Fiscal Year Calendar in Power BI

Using almost the same logic for year and quarter along with a few other columns here is the Pre-Built DAX Code that creates a Calendar Table

Calendar =

--Inputs--
VAR WeekStartsOn = "Mon"
VAR FiscalStartMonth = 4 

--NOTE: Calendar week starts from Monday

--Calculation--
RETURN
    ADDCOLUMNS (
        CALENDARAUTO ( FiscalStartMonth - 1 ),
        "MIndex", MONTH ( [Date] ),
        "FiscalMIndex", MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ),
        "CalMonth", FORMAT ( [Date], "mmm" ),
        "CalQtr", "Q"
            & CEILING ( MONTH ( [Date] ), FiscalStartMonth - 1 ) / ( FiscalStartMonth - 1 ),
        "CalYear", YEAR ( [Date] ),
        "Fiscal Week",
        VAR FiscalFirstDay =
            IF (
                MONTH ( [Date] ) < FiscalStartMonth,
                DATE ( YEAR ( [Date] ) - 1, FiscalStartMonth, 1 ),
                DATE ( YEAR ( [Date] ), FiscalStartMonth, 1 )
            )
        VAR FilteredTableCount =
            COUNTROWS (
                FILTER (
                    SELECTCOLUMNS ( GENERATESERIES ( FiscalFirstDay, [Date] ), "Dates", [Value] ),
                    FORMAT ( [Dates], "ddd" ) = WeekStartsOn
                )
            )
        VAR WeekNos =
            IF (
                FORMAT ( FiscalFirstDay, "ddd" ) <> WeekStartsOn,
                FilteredTableCount + 1,
                FilteredTableCount
            )
        RETURN
            "Week " & WeekNos,
        "Fiscal Qtr", "Q"
            & CEILING ( MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ), 3 ) / 3,
        "Fiscal Year",
        VAR CY =
            RIGHT ( YEAR ( [Date] ), 2 )
        VAR NY =
            RIGHT ( YEAR ( [Date] ) + 1, 2 )
        VAR PY =
            RIGHT ( YEAR ( [Date] ) - 1, 2 )
        VAR FinYear =
            IF ( MONTH ( [Date] ) > ( FiscalStartMonth - 1 ), CY & "-" & NY, PY & "-" & CY )
        RETURN
            FinYear,
        "CalWeekNo", WEEKNUM ( [Date], 2 ),
        "Weekend/Working", IF ( WEEKDAY ( [Date], 2 ) > 5, "Weekend", "Working" ),
        "Day", FORMAT ( [Date], "ddd" ),
        "CustomDate", FORMAT ( [Date], "d/mm" )
    )

How to use this DAX Code

You can simple copy and paste the code in Power BI. To be precise follow the steps

Open one of your existing Power-BI file (with some data)

1. Go to Modeling Tab
2. Click on New Table
3. Paste the code and a new Cal table will be created with 12 columns (FY and Financial Qtrs being the 2 of them)

Resulting Calendar Table in your Power BI would look like this

Things to check –

• Just be sure to load some data with one of the columns as date in the Power BI file before you paste the code. Rest will fall in place automatically
• And do create a relationship between the two tables

 

DOWNLOAD POWER BI & EXCEL FILE

 

More on Date Calculations

• 7 Common Date Problems and their Solutions
• 17 Dates Hacks in Excel
• Find Unique Dates Challenge Question
• Date Calendar Dashboard In Excel using Power Pivot

 

Calendar Tables from Power BI pros

• From Matt Allington – Reusable Calendar Tables in Power Query
• Exhaustive Calendar Table from SqlBI
• Calendar Table from AVI’s Youtube Channel