You’ve probably have faced a data analysis scenario where performing calculations for a year-to-date aggregate meets reporting needs. And of course, for most data analysis cases involving dates, Year-to-date comes to be a widespread de-facto standard of evaluation.
As some of you may have experience, achieving year to data 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. The are many different ways to do achieve this, but none as simple as what is possible when 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 these 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, using DAX there are many various ways to calculate YTD.
Calculating Year To Date Values in Power BI
Let’s take a closer look at TOTALYTD function. This category of functions evaluates an expression over a specified time period.
At first glance, its syntax asks for two mandatory parameters: Expression and Dates.
In order to get the Year-to-date calculation, 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.
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?
For additional date columns like Month, Quarter, and Year, to add these types of columns, 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 from February 1st, 2015 through 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. Every 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. As good as this function is, as mention in my previous post, as it is much quicker, my preference is to create a complete dynamic calendar table in the Power Query section of Power BI Desktop.
Here is a different method of creating a calendar table:
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.
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). Modelling tab > Calculations group > New Column, and here are the formulas for these:
Year = YEAR (‘Calendar’
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’, which can be created in the Relationships view by clicking and dragging the ‘Date’ column label in ‘Sales’ table onto the ‘Date’ column in the ‘Calendar’ table.
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”.
Creating our measures
Time to write some DAX!
Go to Sales table and add a new measure: Home tab > Calculations group > New Measure
And in the formula bar, type: SumOfSales = SUM(Sales[Sales]). As the name denotes, this is a basic sum of the values in the ‘Sales’ column, which can serve both as the basic re-usable measure and as a guide for checking 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, and plot the two measures side-by-side using the Date Hierarchy we just created as 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:
Voilà! On the right hand side is plot of the YTD Total Sales.
More “to-date” functions
There are two more similar functions: TOTALMTD for Month-To-Date totals and 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:
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.
Happy report building!