Create Year-To-Date (YTD) measures in DAX (example uses Xero Invoices data and Power BI)

This article will help you to create Year-To-Date (YTD) measure in DAX.

As basis for examples in this article, we use Xero Invoices data downloaded into Power BI. Note that the approach will work just as well using MYOB Data. The DAX formulas are also compatible with Excel.

There are two main approaches we recommend that can be used to create a Year-To-Date measure. The choice depends on whether you want a simple-yet-hardcoded approach or a flexible approach.

Create a Year-To-Date (YTD) measure in DAX using TOTALYTD function
Create a Year-To-Date (YTD) measure in DAX using a rolling total

If you are unfamiliar with DAX, check out our Introduction Tutorial to DAX.

Sample Invoices data used through the article

This article uses Invoices downloaded from Xero into Power BI using OdataLink.

OdataLink is a powerful tool that allows you to download many endpoints of data from Xero as Odata Feeds directly into Power BI or Excel without the need for any third-party software. For more information on OdataLink, see odatalink.com.

Using OdataLink, we downloaded two tables of information: Invoices and Dates.

We also added a relationship between the Invoices[Date] field and the Dates[Date] field.

Invoices Table for YTD Measures Dates Table for YTD Measures

Lastly, the intention is to produce a chart which includes the total value for the month as a Month-To-Date (MTD) and Year-To-Date (YTD) figure.

Create Year-To-Date (YTD) measure in DAX using TOTALYTD function

The TOTALYTD function is a native function built into DAX to ease calculating Year-To-Date measures.

Before choosing this approach, it is important to understand its pros and cons.

Advantages of the TOTALYTD function

Foremost and by it’s very nature, the TOTALYTD function is optimised to calculate Year-To-Date values. As such, it is very likely that it performs better on larger models than the rolling total approach described below.

In addition, this function is concise and easy to understand. Its very name conveys the intention of what it is calculating. As with any programming, having meaningful names is key to maintaining your dashboards and reports.

Disadvantages of the TOTALYTD function

The biggest drawback of using the TOTALYTD function is that it requires a hardcoded end date parameter. While this might seem benign at first, it means that any dashboard you create may need to be edited to work for different year-ends.

This is particularly common in the financial world where different countries and businesses may use different financial year reporting start and end dates. The TOTALYTD function is simply not suited to this nuance.

This is because the end date must be hardcoded into the function.

As such, initially creating the measure may easy. But adapting it or changing it can be fraught with mistakes, especially if you create many Year-To-Date measures.

When to use the TOTALYTD function

We recommend you use this function for internal projects only as the calculation requirements for Year-To-Date values are not likely to change.

You can also use this function for specific target audiences that are likely to use the same year end dates. For instance, if you are targeting either the Australian, New Zealand or UK market (but not all three).

How to use the TOTALYTD function

The TOTALYTD function accepts 4 parameters.

The source value (or expression) from which to calculate.
The date column.
The filters to includes all records.
The year end date expressed as the month and day.

Its syntax is:

MeasureName = TOTALYTD(source_value, date_column, filters, year_end)

Using our example data, the measure that produces a year-to-date value ending on the 30th of June is:

TotalYTD1 = TOTALYTD(SUM(Invoices[Total]), Dates[Date], ALL(Dates), "06/30")

The above formula sums the Invoices[Total] field and uses the Dates[Date] field as the key column. Finally, it sets the end of the year to be the 30th of June.

As you can see, this formula is very concise and easy to understand. The only drawback is the hardcoded year end parameter.

Create Year-To-Date (YTD) measure in DAX using a rolling total

The rolling total approach uses relationships between source table and a dates table to produce the desired results.

In our example, this is the Invoices table and the Dates Table.

Before choosing this approach, it is important to understand its pros and cons.

Advantages of using rolling total for YTD measures

The rolling total approach to creating a Year-To-Date measure scales the most and is the easiest to maintain. Once built, it will automatically adapt itself to different financial year reporting start and end dates.

This is partly due to the nature of the OdataLink Dates, DatesByMonths and DatesByYear endpoints. These endpoints are generated based on the financial year makeup of Xero or MYOB.

This means you do not hardcode any fields into your formulas.

Another major advantage of this approach is that it is consistent for all rolling totals. While it can be used to produce Year-To-Date values, it can also be used to provide any other type of rolling total. This means that this approach can be learned once and applied to many other problems.

Disadvantages of using a rolling total for YTD measures

The only disadvantage of this approach is to do with the complexity of the formula. It is harder to read and understand how it works.

How to use a rolling total to create a Year-To-Date total

To create a Year-To-Date measure using the rolling total approach, we use the CALCULATE and FILTER functions.

To make this approach work, we specify:

The source value (or expression) from which to calculate.
The dates table.
The financial year field of the dates table.
The date field of the dates table.

The syntax is:

MeasureName = 
    CALCULATE(
        SUM(source_value), 
        FILTER(
            ALL(dates_table), 
            (dates_table[financial_year_field] = MAX(dates_table[financial_year_field])) && 
            (dates_table[date_field] <= MAX(dates_table[date_field]))
        )
    )

Using our example data, the measure that produces a year-to-date value ending on the 30th of June is:

TotalYTD2 = 
    CALCULATE(
        SUM(Invoices[Total]), 
        FILTER(
            ALL(Dates), 
            (Dates[FinancialYear] = MAX(Dates[FinancialYear])) && 
            (Dates[Date] <= MAX(Dates[Date]))
        )
    )

The above formula sums the Invoices[Total] field and filters the Dates table. It will sum all values belonging to the same financial year but whose date is less than or equal to the invoices date.

While the code might look counter-intuitive, it works because there is a relationship between the Invoices table and the Dates table. The filter compares the Dates record based on the relationship with all other Dates record.

The following comparison controls the rolling total.

(Dates[Date] <= MAX(Dates[Date])

The snippet above compares the Dates[Date] field from the relationship with all other records in the dates table so longer as they are less than or equal. This essentially controls the rolling total and ensures that data on July data will sum into August data, but not the other way around.

The following comparison resets the rolling total.

(Dates[FinancialYear] = MAX(Dates[FinancialYear))

The snippet above compares the Dates[FinancialYear] field from the relationship with all other records in the dates table so long as they belong to the same financial year. This essentially ensures that the rolling total is constrained by financial year. In other words, June data will not sum into July data (assuming a financial year that runs July to June).