DAX and data models
How to Create YTD Calculations in Power BI
How to write a YTD measure in Power BI with TOTALYTD or DATESYTD, plus prior-year YTD, fiscal years and a fix for future months.
Sajagan Thirugnanam
·
Updated
To create a YTD calculation in Power BI, write a measure with TOTALYTD, or with CALCULATE and DATESYTD, and point it at the date column of a marked date table. A Power BI YTD measure then adds up a value from the start of the year to whatever date is selected in the report, based on its filters and slicers. This post shows each YTD function in Power BI, how to get prior-year YTD, how to use a fiscal year, and how to stop YTD from showing values for months that have not happened yet.
What a YTD calculation is
YTD stands for year to date. It is the total of a value from the first day of the year up to a given date. If a report shows 2026-06-22, the YTD value sums 2026-01-01 to 2026-06-22. If the report shows a whole month, such as 2026-03, the YTD value sums 2026-01-01 to 2026-03-31.
The year does not have to start on the first day of the calendar year. Many companies run a fiscal year that ends mid-year, for example on 6/30 or 3/31 (month/day). A YTD calculation then starts on the first day of that fiscal year.
YTD answers two common questions. How far are we through the year's target? And how does the year so far compare with the same point one year earlier?
Before you start: a marked date table
Every YTD measure in this post uses a separate date table called 'Date', with one row per day. The classic time intelligence functions, which include TOTALYTD and DATESYTD, need that table to be marked as a date table. Power BI checks that the date column:
Contains one row per date, with no duplicates.
Contains no blank values.
Has contiguous dates from the first to the last, with no gaps.
To mark it, select the table in the Data pane and choose Table tools > Mark as date table, then pick the date column. Relate 'Date'[Date] to the date column of your fact table, one-to-many. Our guide on how to create date tables in Power BI shows how to build one.
Do not point YTD functions at a date column on the fact table, such as Sales[OrderDate]. That column has gaps on days with no sales. And when a visual filters the date table, that filter stays in place, so the measure returns only the selected month instead of the year to date.
How to use the TOTALYTD DAX function in Power BI
TOTALYTD is the shortest way to write a YTD measure in Power BI. Its syntax is:
TOTALYTD ( <expression>, <dates> [, <filter>] [, <year_end_date>The arguments are:
expression: the value to total, such as
SUM ( Sales[Revenue] )or an existing measure.dates: the date column of your date table.
filter (optional): a filter to apply, with the same rules as a
CALCULATEfilter.year_end_date (optional): the last day of the year as a text string, such as
"6/30". The default is 12/31, the end of the calendar year.
A basic YTD revenue measure:
Revenue = SUM ( Sales[Revenue] )
YTD Revenue =
TOTALYTD ( [Revenue], 'Date'[Date] )Writing the base measure first and reusing it keeps each YTD measure short. To total only one product type, add a filter:
YTD Revenue Type C =
TOTALYTD (
[Revenue],
'Date'[Date],
'Product'[Product Type] = "C"
)How to calculate YTD in Power BI with DATESYTD and CALCULATE
TOTALYTD is a shortcut for CALCULATE with DATESYTD. DATESYTD returns the list of dates from the start of the year to the last date in the current context. CALCULATE then evaluates the measure over those dates.
YTD Revenue =
CALCULATE (
[Revenue],
DATESYTD ( 'Date'[Date] )
)This version gives the same result as the TOTALYTD measure. It is easier to extend, because you can add more filters to the same CALCULATE. Our post on the CALCULATE function explains how those filters combine.
How to calculate a fiscal year YTD
Both functions take a year-end date as a text string. For a fiscal year that ends on 6/30 (month/day):
Fiscal YTD Revenue =
TOTALYTD ( [Revenue], 'Date'[Date], "6/30" )
Fiscal YTD Revenue (DATESYTD) =
CALCULATE (
[Revenue],
DATESYTD ( 'Date'[Date], "6/30" )
)The year part of the string is ignored. The string is read in the locale the model was created in. In an English (United States) model, "6/30" works, and it is the form Microsoft's own examples use. In a locale that writes the day first, the string may need to be "30/6". Test the measure against a known fiscal year total after you write it.
How to calculate the previous year's YTD
To compare with the prior year, shift the dates back one year with SAMEPERIODLASTYEAR and reuse the YTD measure:
PY YTD Revenue =
CALCULATE (
[YTD Revenue],
SAMEPERIODLASTYEAR ( 'Date'[Date] )
)You can also pass the shifted dates straight to TOTALYTD. This is valid because the dates argument accepts a table of dates:
PY YTD Revenue =
TOTALYTD ( [Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )Both measures return the same value. The first one is easier to read, because it says "the YTD measure, one year earlier". With both measures in place, YTD growth is one more measure:
YTD Revenue Growth % =
DIVIDE (
[YTD Revenue] - [PY YTD Revenue],
[PY YTD Revenue]
)DIVIDE returns blank instead of an error when the prior-year value is zero or blank. Format the measure as a percentage.
How to stop YTD from showing future months
A date table usually runs to the end of the current year. A YTD measure returns a value for every date in that table. So a line chart of YTD revenue by month keeps going after the last sale, as a flat line at the latest total. A matrix shows the same number repeated in empty future months.
To hide those months, return a value only for periods that start on or before the last date with sales:
YTD Revenue to Last Sale =
VAR LastSaleDate =
CALCULATE ( MAX ( Sales[OrderDate] ), ALL ( Sales ) )
RETURN
IF (
MIN ( 'Date'[Date] ) <= LastSaleDate,
[YTD Revenue]
)ALL ( Sales ) removes every filter on the sales table, including filters that come from the date table. So LastSaleDate is the last order date in the whole model. For any period that starts after it, IF returns blank and the visual shows nothing.
Calculating YTD without time intelligence functions
You can also write YTD with CALCULATE and FILTER alone. This is useful when you want to see exactly which dates are included.
YTD Revenue (manual) =
VAR LastVisibleDate = MAX ( 'Date'[Date] )
RETURN
CALCULATE (
[Revenue],
FILTER (
ALL ( 'Date' ),
'Date'[Date] <= LastVisibleDate
&& YEAR ( 'Date'[Date] ) = YEAR ( LastVisibleDate )
)
)The measure finds the last date visible in the current context. It then removes all filters from the date table and keeps only the dates in the same calendar year, up to that last date. For a calendar year, the result matches DATESYTD. The manual version does not handle a fiscal year on its own. For that, filter on a fiscal year column in your date table instead of YEAR.
Power BI also has a newer, calendar-based time intelligence feature, in preview, where you define calendars on the date table. With it, TOTALYTD and DATESYTD can take a calendar name instead of a date column. The classic pattern in this post works in every current version of Power BI Desktop.
Using YTD measures in Power BI visuals and reports
YTD measures work in most visuals. Common uses:
Card or KPI visual: YTD Revenue as a single number, with PY YTD Revenue as the comparison or target.
Line or area chart: YTD Revenue by month, from the date table, shows how the year builds up. Add PY YTD Revenue as a second line to compare the two years.
Clustered column chart or matrix: YTD and prior-year YTD side by side by region or product, with YTD Revenue Growth % as a third column.
To set them up:
Put the YTD measure in the visual's Values or Y-axis well.
Put a column from the date table, such as
'Date'[Month], on the axis or rows. Never use a date column from the fact table.Add a slicer on
'Date'[Year]so readers can pick the year.Check one month against a known total before you publish the report.
For how YTD fits with month-to-date, quarter-to-date and rolling totals, see our time intelligence guide. YTD values must be measures, not calculated columns, because they depend on the dates a reader selects. Our post on measures vs calculated columns explains the difference. To place these visuals in a finished page, see building Power BI reports step by step.
If you would rather not write DAX, Quick measures > Time intelligence > Year-to-date total generates a TOTALYTD measure for you. It is a good way to see the pattern once.
FAQs
What is YTD in DAX?
In DAX, YTD is a calculation that totals a value from the start of the year up to the last date in the current filter context. DAX has two functions for it. TOTALYTD returns the YTD value directly. DATESYTD returns the YTD dates, which you pass to CALCULATE. Both need a date column from a marked date table, and both accept an optional fiscal year-end date.
What is the YTD calculation?
A YTD, or year-to-date, calculation adds up a metric from the first day of the year to a chosen date. For example, YTD revenue on 2026-06-22 is all revenue from 2026-01-01 to 2026-06-22. For a fiscal year, the start date is the first day of the fiscal year. In Power BI, you write it as a measure so it changes with the dates selected in the report.
Sources
TOTALYTD function (DAX) - Microsoft Learn
DATESYTD function (DAX) - Microsoft Learn
Set and use date tables in Power BI Desktop - Microsoft Learn
To create a YTD calculation in Power BI, write a measure with TOTALYTD, or with CALCULATE and DATESYTD, and point it at the date column of a marked date table. A Power BI YTD measure then adds up a value from the start of the year to whatever date is selected in the report, based on its filters and slicers. This post shows each YTD function in Power BI, how to get prior-year YTD, how to use a fiscal year, and how to stop YTD from showing values for months that have not happened yet.
What a YTD calculation is
YTD stands for year to date. It is the total of a value from the first day of the year up to a given date. If a report shows 2026-06-22, the YTD value sums 2026-01-01 to 2026-06-22. If the report shows a whole month, such as 2026-03, the YTD value sums 2026-01-01 to 2026-03-31.
The year does not have to start on the first day of the calendar year. Many companies run a fiscal year that ends mid-year, for example on 6/30 or 3/31 (month/day). A YTD calculation then starts on the first day of that fiscal year.
YTD answers two common questions. How far are we through the year's target? And how does the year so far compare with the same point one year earlier?
Before you start: a marked date table
Every YTD measure in this post uses a separate date table called 'Date', with one row per day. The classic time intelligence functions, which include TOTALYTD and DATESYTD, need that table to be marked as a date table. Power BI checks that the date column:
Contains one row per date, with no duplicates.
Contains no blank values.
Has contiguous dates from the first to the last, with no gaps.
To mark it, select the table in the Data pane and choose Table tools > Mark as date table, then pick the date column. Relate 'Date'[Date] to the date column of your fact table, one-to-many. Our guide on how to create date tables in Power BI shows how to build one.
Do not point YTD functions at a date column on the fact table, such as Sales[OrderDate]. That column has gaps on days with no sales. And when a visual filters the date table, that filter stays in place, so the measure returns only the selected month instead of the year to date.
How to use the TOTALYTD DAX function in Power BI
TOTALYTD is the shortest way to write a YTD measure in Power BI. Its syntax is:
TOTALYTD ( <expression>, <dates> [, <filter>] [, <year_end_date>The arguments are:
expression: the value to total, such as
SUM ( Sales[Revenue] )or an existing measure.dates: the date column of your date table.
filter (optional): a filter to apply, with the same rules as a
CALCULATEfilter.year_end_date (optional): the last day of the year as a text string, such as
"6/30". The default is 12/31, the end of the calendar year.
A basic YTD revenue measure:
Revenue = SUM ( Sales[Revenue] )
YTD Revenue =
TOTALYTD ( [Revenue], 'Date'[Date] )Writing the base measure first and reusing it keeps each YTD measure short. To total only one product type, add a filter:
YTD Revenue Type C =
TOTALYTD (
[Revenue],
'Date'[Date],
'Product'[Product Type] = "C"
)How to calculate YTD in Power BI with DATESYTD and CALCULATE
TOTALYTD is a shortcut for CALCULATE with DATESYTD. DATESYTD returns the list of dates from the start of the year to the last date in the current context. CALCULATE then evaluates the measure over those dates.
YTD Revenue =
CALCULATE (
[Revenue],
DATESYTD ( 'Date'[Date] )
)This version gives the same result as the TOTALYTD measure. It is easier to extend, because you can add more filters to the same CALCULATE. Our post on the CALCULATE function explains how those filters combine.
How to calculate a fiscal year YTD
Both functions take a year-end date as a text string. For a fiscal year that ends on 6/30 (month/day):
Fiscal YTD Revenue =
TOTALYTD ( [Revenue], 'Date'[Date], "6/30" )
Fiscal YTD Revenue (DATESYTD) =
CALCULATE (
[Revenue],
DATESYTD ( 'Date'[Date], "6/30" )
)The year part of the string is ignored. The string is read in the locale the model was created in. In an English (United States) model, "6/30" works, and it is the form Microsoft's own examples use. In a locale that writes the day first, the string may need to be "30/6". Test the measure against a known fiscal year total after you write it.
How to calculate the previous year's YTD
To compare with the prior year, shift the dates back one year with SAMEPERIODLASTYEAR and reuse the YTD measure:
PY YTD Revenue =
CALCULATE (
[YTD Revenue],
SAMEPERIODLASTYEAR ( 'Date'[Date] )
)You can also pass the shifted dates straight to TOTALYTD. This is valid because the dates argument accepts a table of dates:
PY YTD Revenue =
TOTALYTD ( [Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )Both measures return the same value. The first one is easier to read, because it says "the YTD measure, one year earlier". With both measures in place, YTD growth is one more measure:
YTD Revenue Growth % =
DIVIDE (
[YTD Revenue] - [PY YTD Revenue],
[PY YTD Revenue]
)DIVIDE returns blank instead of an error when the prior-year value is zero or blank. Format the measure as a percentage.
How to stop YTD from showing future months
A date table usually runs to the end of the current year. A YTD measure returns a value for every date in that table. So a line chart of YTD revenue by month keeps going after the last sale, as a flat line at the latest total. A matrix shows the same number repeated in empty future months.
To hide those months, return a value only for periods that start on or before the last date with sales:
YTD Revenue to Last Sale =
VAR LastSaleDate =
CALCULATE ( MAX ( Sales[OrderDate] ), ALL ( Sales ) )
RETURN
IF (
MIN ( 'Date'[Date] ) <= LastSaleDate,
[YTD Revenue]
)ALL ( Sales ) removes every filter on the sales table, including filters that come from the date table. So LastSaleDate is the last order date in the whole model. For any period that starts after it, IF returns blank and the visual shows nothing.
Calculating YTD without time intelligence functions
You can also write YTD with CALCULATE and FILTER alone. This is useful when you want to see exactly which dates are included.
YTD Revenue (manual) =
VAR LastVisibleDate = MAX ( 'Date'[Date] )
RETURN
CALCULATE (
[Revenue],
FILTER (
ALL ( 'Date' ),
'Date'[Date] <= LastVisibleDate
&& YEAR ( 'Date'[Date] ) = YEAR ( LastVisibleDate )
)
)The measure finds the last date visible in the current context. It then removes all filters from the date table and keeps only the dates in the same calendar year, up to that last date. For a calendar year, the result matches DATESYTD. The manual version does not handle a fiscal year on its own. For that, filter on a fiscal year column in your date table instead of YEAR.
Power BI also has a newer, calendar-based time intelligence feature, in preview, where you define calendars on the date table. With it, TOTALYTD and DATESYTD can take a calendar name instead of a date column. The classic pattern in this post works in every current version of Power BI Desktop.
Using YTD measures in Power BI visuals and reports
YTD measures work in most visuals. Common uses:
Card or KPI visual: YTD Revenue as a single number, with PY YTD Revenue as the comparison or target.
Line or area chart: YTD Revenue by month, from the date table, shows how the year builds up. Add PY YTD Revenue as a second line to compare the two years.
Clustered column chart or matrix: YTD and prior-year YTD side by side by region or product, with YTD Revenue Growth % as a third column.
To set them up:
Put the YTD measure in the visual's Values or Y-axis well.
Put a column from the date table, such as
'Date'[Month], on the axis or rows. Never use a date column from the fact table.Add a slicer on
'Date'[Year]so readers can pick the year.Check one month against a known total before you publish the report.
For how YTD fits with month-to-date, quarter-to-date and rolling totals, see our time intelligence guide. YTD values must be measures, not calculated columns, because they depend on the dates a reader selects. Our post on measures vs calculated columns explains the difference. To place these visuals in a finished page, see building Power BI reports step by step.
If you would rather not write DAX, Quick measures > Time intelligence > Year-to-date total generates a TOTALYTD measure for you. It is a good way to see the pattern once.
FAQs
What is YTD in DAX?
In DAX, YTD is a calculation that totals a value from the start of the year up to the last date in the current filter context. DAX has two functions for it. TOTALYTD returns the YTD value directly. DATESYTD returns the YTD dates, which you pass to CALCULATE. Both need a date column from a marked date table, and both accept an optional fiscal year-end date.
What is the YTD calculation?
A YTD, or year-to-date, calculation adds up a metric from the first day of the year to a chosen date. For example, YTD revenue on 2026-06-22 is all revenue from 2026-01-01 to 2026-06-22. For a fiscal year, the start date is the first day of the fiscal year. In Power BI, you write it as a measure so it changes with the dates selected in the report.
Sources
TOTALYTD function (DAX) - Microsoft Learn
DATESYTD function (DAX) - Microsoft Learn
Set and use date tables in Power BI Desktop - Microsoft Learn
Show us your report nobody trusts.
1 · A 30-minute call.
2 · We look at your current reports together.
3 · We tell you what we would do.
Show us your report nobody trusts.
1 · A 30-minute call.
2 · We look at your current reports together.
3 · We tell you what we would do.
Show us your report nobody trusts.
1 · A 30-minute call.
2 · We look at your current reports together.
3 · We tell you what we would do.
CaseWhen is a Berlin BI consultancy that builds reporting that leaders can trust, on the Microsoft stack: Power BI, Fabric and Azure.
Berlin, Germany
Free tools
CaseWhen is a Berlin BI consultancy that builds reporting that leaders can trust, on the Microsoft stack: Power BI, Fabric and Azure.
Berlin, Germany
Free tools
CaseWhen is a Berlin BI consultancy that builds reporting that leaders can trust, on the Microsoft stack: Power BI, Fabric and Azure.
Berlin, Germany