Add Indian financial year and quarter columns in Excel, pandas, DAX

The three columns every Indian report needs

The financial year runs 1 April to 31 March, so a calendar-year report puts January to March in the wrong year for budgets, targets and audits. You need:

The month number is the column people skip, and it is the one that causes the most visible bug: a chart that runs April, August, December, February because the axis sorted names alphabetically.

Excel formulas, with the reasoning

Put dates in column A of an Excel table.

B  FY start year   =YEAR(A2)-(MONTH(A2)<4)
C  FY label        ="FY "&B2&"-"&RIGHT(B2+1,2)
D  FY month no.    =MOD(MONTH(A2)-4,12)+1
E  FY quarter      ="Q"&INT((D2-1)/3)+1

MONTH(A2)<4 is TRUE for January, February and March. Excel treats TRUE as 1 in arithmetic, so those months subtract one from the year. MOD(MONTH(A2)-4,12) turns April into 0 and January into 9; Excel's MOD takes the sign of the divisor, so the negative numbers from January to March wrap round correctly. Adding 1 gives April = 1 and March = 12.

An alternative some teams find easier to read shifts the date back three months and then uses calendar functions: =YEAR(EDATE(A2,-3)) gives the FY start year, and =ROUNDUP(MONTH(EDATE(A2,-3))/3,0) gives the quarter. Both approaches return identical results; pick one per workbook so nobody has to decode two.

For a month name that sorts correctly in a pivot table, use the month number as a helper field, or create a custom list (File > Options > Advanced > Edit Custom Lists) running April to March.

pandas: let the Q-MAR period do the work

pandas has fiscal quarters built in. A period with frequency Q-MAR means "quarters of a year that ends in March", and its qyear is the year in which that FY ends.

import pandas as pd

# Synthetic invoice dates spanning a financial-year boundary.
sales = pd.DataFrame({
    "invoice_date": pd.to_datetime(["2026-03-31", "2026-04-01", "2026-06-30",
                                    "2026-07-01", "2026-12-15", "2027-01-02",
                                    "2027-03-31"]),
    "amount": [42000, 18000, 25500, 31000, 12750, 40000, 9800],
})

p = sales["invoice_date"].dt.to_period("Q-MAR")   # quarters of a year ending in March
fy_end = p.dt.qyear                                # 2027 for any date Apr 2026 - Mar 2027
sales["fy"] = "FY " + (fy_end - 1).astype(str) + "-" + (fy_end % 100).astype(str).str.zfill(2)
sales["fy_quarter"] = "Q" + p.dt.quarter.astype(str)
sales["fy_month_no"] = (sales["invoice_date"].dt.month - 4) % 12 + 1   # April = 1
print(sales.to_string(index=False))

# Cross-check against the plain arithmetic rule used in the Excel formula.
start = sales["invoice_date"].dt.year - (sales["invoice_date"].dt.month < 4)
assert (start == fy_end - 1).all()
print(sales.groupby(["fy", "fy_quarter"])["amount"].sum())

31 March 2026 lands in FY 2025-26 Q4, and the next day starts FY 2026-27 Q1. The zfill(2) matters whenever the ending year's last two digits start with zero: without it, historical data would show "FY 2008-9" instead of FY 2008-09. Python's % also wraps negatives the Excel way, which is why the month-number line needs no special case.

DAX: build it into the date table

In Power BI, add these as calculated columns on your date table rather than on the fact table, so every visual and measure shares one definition.

FY Start = YEAR ( 'Date'[Date] ) - IF ( MONTH ( 'Date'[Date] ) < 4, 1, 0 )

FY = "FY " & 'Date'[FY Start] & "-" & RIGHT ( 'Date'[FY Start] + 1, 2 )

FY Month No = MOD ( MONTH ( 'Date'[Date] ) - 4, 12 ) + 1

FY Quarter = "Q" & ROUNDUP ( 'Date'[FY Month No] / 3, 0 )

Sales FYTD = TOTALYTD ( [Sales], 'Date'[Date], "3/31" )

Then select the month name column and set Sort by column to FY Month No. Do the same for a "FY Quarter" label if you ever add text like "Q1 (Apr-Jun)". The last argument of TOTALYTD is the year-end date; without it, year-to-date resets on 1 January and your April-to-date figure is silently wrong for the last quarter of every year.

Test with the boundary dates

Whatever tool you use, test the formula against five dates before trusting it: 31 March, 1 April, 30 June, 1 January and 29 February of a leap year. If those five come out right, the rule is right. Most wrong versions fail on 31 March or 1 January, which is why the synthetic data above includes both.

Getting the calendar right is the first piece of any Indian management report; the next piece is showing amounts the way your readers expect, covered in Show numbers in lakhs and crores in Excel, Power BI and Python. Date tables, DAX time intelligence and reporting to an April-March year are taught with real practice files in our Data Analyst course.