STARTOFYEAR
Returns the first date of the year that contains the dates in the filter context, with an optional fiscal year end, as a one-row table.
STARTOFYEAR(<dates> [, <year_end_date>])Returns: A table with one date, used as a filter inside CALCULATE.
What it does
STARTOFYEAR returns the first day of the year in context. With a year end date, it returns the first day of the fiscal year. Inside CALCULATE it limits a measure to that day. Finance teams use it to read the opening balance of the year, such as the cash or equity balance at the start of the fiscal year.
Syntax and arguments
- dates: the date column of the Calendar table. Required.
- year_end_date: the fiscal year end as text, such as "6/30". Optional. Leave it out for a calendar year.
Example
A company with a June 30 year end wants the cash balance at the start of each fiscal year, for the annual cash roll-forward.
Cash at Start of FY =
CALCULATE(
SUM('Bank_Balances'[Balance]),
STARTOFYEAR('Calendar'[Date], "6/30")
)
In any month of fiscal 2027, the filter is July 1, 2026. The measure returns the cash balance on that day, which should equal the closing balance of June 30, 2026.
Example: a second, different use
The start of the calendar year for a calendar-year company, used as the first date of a year to date view for a snapshot table.
Equity at Start of Year = CALCULATE(SUM('Equity_Balances'[Amount]), STARTOFYEAR('Calendar'[Date]))
Things to watch
- STARTOFYEAR picks one day. Use TOTALYTD or DATESYTD for a total from the start of the year.
- The year end text must match the company's fiscal year end.
- The first day must have a row in the balance table, or the result is blank.
- Keep it inside CALCULATE. On its own, it is not a number.
In business terms
STARTOFYEAR supports the opening balance for the annual roll-forward, the start-of-year equity figure for the audit, and the year opening check that ties one year's statements to the next.