📎 This article includes 2 downloadable practice files ↓
In this article
Reports group by month, quarter and financial year, and Indian FY runs April to March, which no tool understands out of the box. Power Query adds these columns once, and they refresh with the data.
Built-in date columns
Select the Date column (type Date) › Add Column › Date: Year, Month (number or name), Quarter, Week of Year, Day name, Start/End of Month. These follow the calendar year.
Financial year and FY quarter
Add Column › Custom Column:
FYStart: if Date.Month([Date]) >= 4 then Date.Year([Date]) else Date.Year([Date]) - 1
FY: "FY " & Text.From([FYStart]) & "-" & Text.End(Text.From([FYStart] + 1), 2)
FYQuarter: "Q" & Text.From(Number.IntegerDivide(Number.Mod(Date.Month([Date]) + 8, 12), 3) + 1)A date in May 2026 → FY 2026-27, Q1; February 2027 → FY 2026-27, Q4.
Days between dates
DaysOpen: Duration.Days([Delivered] - [OrderDate])
DueDate: Date.AddDays([InvoiceDate], 30)
Age: Duration.Days(#date(2026, 9, 30) - [DueDate])Subtracting dates gives a duration; Duration.Days turns it into a number.
A calendar table
Power BI and Power Pivot work best with a separate table of every date. Home › New Source › Blank Query, open the Advanced Editor and paste:
let
Start = #date(2026, 4, 1),
End = #date(2027, 3, 31),
Dates = List.Dates(Start, Duration.Days(End - Start) + 1, #duration(1, 0, 0, 0)),
T = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}),
Typed = Table.TransformColumnTypes(T, {{"Date", type date}}),
Mon = Table.AddColumn(Typed, "Month", each Date.ToText([Date], "MMM"), type text),
FYQ = Table.AddColumn(Mon, "FYQuarter", each "Q" & Text.From(Number.IntegerDivide(Number.Mod(Date.Month([Date]) + 8, 12), 3) + 1), type text)
in
FYQ365 rows, one per day. Add holiday flags by merging with a holiday list (Lesson 5).
Where beginners go wrong
| Problem | Cause |
|---|---|
| Date functions error | Column is text or datetime; set type to Date |
| Quarter shows calendar quarters | Built-in Quarter is Jan-Mar; use the FY formula |
| Month names sort alphabetically | Also add a month number or FY month index to sort by |
Practice
Download the source file(s) and the expected-results workbook below. Build the query in Excel (Data › Get Data), load it to a sheet, and compare your row count and totals with the Checks sheet.
📎 Practice files for this article
- 🧾pq-07-sales.csvThe same 180 orders (dates April-September 2026).⬇ CSV · 10 KB
- 📗Expected resultsWhat your query should produce, with check totals.⬇ XLSX · 18 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.