You’ve probably faced a data analysis scenario of having to calculate year-to-date values to meet reporting specifications. And of course, for most data analysis cases involving dates, YTD is a widespread de-facto standard of evaluation.

As some of you may have experienced, achieving year-to-date analysis, based on a selected year and period, in Excel is not straight forward. I have memories of building this type of aggregation, based on a selection, using the Excel Choose function. There are various ways to achieve this, but none as simple as using Power Pivot in Excel or Power BI.

Fortunately, along with the appearance of Power Pivot and DAX, a bunch of useful tools and functions showed up, known as ‘Time-intelligence’ functions. One of which is TOTALYTD. The Year-To-Date calculation of Power Pivot and Power BI Desktop that serves as a headache-pill for the former Year-to-date calculation methods in Excel. Please note that when using DAX there are many ways to calculate YTD.

Calculating Year To Date Values in Power BI

Let’s take a closer look at the TOTALYTD function. This category of functions evaluates an expression over a specified time period.

Power BI Tips - Calculate year-to-date

At first glance, its syntax asks for two mandatory parameters: Expression and Dates.

In order to calculate year-to-date values, you only need to specify ‘what to calculate’, and for ‘which set of dates’.

Here’s an example of how we can use the TOTALYTD function:

Let’s suppose you have a table with the daily sales of John Doe’s company for 2016 and you need to plot the Monthly Year-to-date sales for the year:

ytd2

How do we do this? Firstly, we will need;

A Calendar Table (or Date Table):

You might have noticed the presence of a separate Calendar table in the above image. As per my previous post on ‘Sort by Month Name’, there are many ways to build a calendar table. Previously, I showed how to do this using the ‘CALENDARAUTO’ function. Why is the function useful?

To add additional date columns like Month, Quarter, and Year, a date column must have one row for every day for each year included in the date table.

For example, if your dataset has dates ranging between February 1st, 2015 through to October 30th, 2015, and you report on a calendar year, you will need a date table with at least a date range from January 1st, 2015 through December 31st, 2015. Each year in your date table must contain all of the days for each year.

The ‘CALENDARAUTO’ function returns a table with a single column called ‘Date’, this contains a contiguous set of dates, calculated automatically based on data in the model. Even though this function is good due to its speed, my preference, as mentioned in my previous post, is to create a complete dynamic calendar table in the Power Query section of Power BI Desktop.

An alternative function for creating a calendar table is the following:

Click on Modelling tab > Calculations group > New Table.

In the formula bar, write:

Calendar = CALENDAR (“01-01-2016″,”12-31-2016”)

A table with all the dates between the two specified dates will be created.

ytd3

In my previous post, I provide a full formula sheet for building additional calendar table columns.

Let’s add some other columns, such as ‘Year’, ‘MonthNumber’, and ‘MonthPrefix’ (first three letters):

Modeling tab > Calculations group > New Column, and here are the formulas for these:

Year = YEAR (‘Calendar'[Date])

MonthNumber = MONTH (‘Calendar’ [Date])

MonthPrefix = FORMAT (‘Calendar [Date] ‘, “MMM”)

Now we need to create a relationship between our ‘Calendar’ table and the ‘Sales’ table. Use a many-to-one relationship from ‘Sales’ to ‘Calendar’. The relationship can be created in the Relationships view by clicking and dragging the ‘Date’ column label in the ‘Sales’ table onto the ‘Date’ column in the ‘Calendar’ table.

ytd4

If you like, you can create a hierarchy with these columns in the Calendar table, here’s how:

In Report view > Fields pane, select the “Year” field of Calendar table and right-click on it, then click “New hierarchy”. Rename the hierarchy to “Date Hierarchy” by right-clicking on it and choosing Rename.

Now select “MonthNumber”, “MonthPrefix”, and “Date” fields of the Calendar table, and add them to the hierarchy by right-clicking on them and choosing “Add to Date Hierarchy”.

ytd5

Creating measures to be used when we calculate year-to-date values:

Time to write some DAX!

Go to Sales table and add a new measure:

Home tab > Calculations group > New Measure

In the formula bar, type: SumOfSales = SUM(Sales[Sales]).

As the name suggests, this is a basic sum of the values in the ‘Sales’ column. This Sum of Sales measure can serve both as the basic re-usable measure and as a guide for checking the correctness of our YTD Sales calculation.

Our desired year-to-date field will then be another measure based on [SumOfSales]:

YTDSumOfSales = TOTALYTD([SumOfSales],’Calendar'[Date])

The simplest naming convention serves as an example only; these measures can have any name.

Next, go to Report view. When you are in the report view, plot the two measures side-by-side. The date hierarchy that was just created should be used for the Axis of the charts. Then, drill down to “MonthPrefix” level by clicking on the little circled arrows at the top-left corner of each chart to get this:

Calculate Year-to-date Values_ charts

Voilà! On the right-hand side is a plot of the YTD Total Sales.

More “to-date” functions:

There are two more similar functions:

  1. TOTALMTD: for Month-To-Date totals, and
  2. TOTALQTD: for Quarter-To-Date totals.

These can be used as measures in our model as well:

QTDSumOfSales = TOTALQTD([SumOfSales],’Calendar'[Date])

MTDSumOfSales = TOTALMTD([SumOfSales],’Calendar'[Date])

After plotting these MTD and QTD measures, and with a bit of formatting, a final picture shows us what we have built so far:

Calculate Year-to-date Values

It might be helpful to know that you can accomplish the same thing using CALCULATE and other time-intelligence functions.

For example:

= TOTALMTD (Expression, Date_Column [, SetFilter])

is precisely the same as:

= CALCULATE (Expression, DATESMTD (Date_Column)[, SetFilter])

We have not covered the CALCULATE or the DATESMTD functions, I’ll save this for another post.

Please leave comments, or, if you require any further help, contact us.

For more great tips, check out our blog page.

Happy report building!