Downloads
Power Query MDateBeginner

Date.StartOfYear

Returns January 1 of the year that contains a date.

Syntax
Date.StartOfYear(dateTime as any) as date

Returns: January 1 of that year as a date, or null when the input is null.

What it does

Date.StartOfYear returns January 1 of the calendar year that holds a date. It is the start of a year to date range and the basis of annual totals. For a fiscal year that starts in another month, the fiscal example shows how to move the start to that month.

Syntax and arguments

Example

A year to date revenue total runs from the start of the calendar year to the report date. The start date is taken from the report date, so the range moves when the report date changes.

let
    ReportDate = #date(2026, 10, 29),
    YearStart = Date.StartOfYear(ReportDate),
    YearEnd = Date.EndOfYear(ReportDate)
in
    {YearStart, YearEnd}

The year to date range runs from January 1, 2026 to the report date, and the full year ends on December 31, 2026. A year to date filter uses YearStart and ReportDate as its two limits.

Example: a second, different use

Find the start of a fiscal year that begins on July 1, so a fiscal year to date total uses the right range.

let
    Invoice = #date(2026, 10, 29),
    FiscalYearStart = Date.AddMonths(Date.StartOfYear(Date.AddMonths(Invoice, 6)), -6)
in
    FiscalYearStart

The result is July 1, 2026. Adding six months moves October into the calendar year 2027, where January is the start of that calendar year. Subtracting six months moves that January back to July 2026.

Things to watch

In business terms

Year to date revenue, expense and budget comparisons all start on the first day of the year. For a company with a fiscal year, getting that start date right is what makes the year to date figure match the annual report.

Related functions

All Power Query M functions · View as Markdown · M basics