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.
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:
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.
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.
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 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:
Voilà! On the right-hand side is a 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.
For more great tips, check out our blog page.
Happy report building!
Great post, thank you. How would you also add a “Previous” year to date calculation – so you could compare this year to date with the previous?
Hi Lee,
Thanks for the comment! Apologies for the late response, only just saw this…
There are many ways to achieve this through DAX, one of them is to use the SAMEPERIODLASTYEAR function.
Here is an example:
PY Sales YTD = CALCULATE([YTDSumOfSales],
SAMEPERIODLASTYEAR(‘Calendar'[Date])
)
However, if your report has a year slicer (filter), as the user might select more than one year, that formula would not be suitable.
The following formula evaluates this, and, if more than one year is selected, it throws a blank instead:
PY Sales YTD = IF(HASONEVALUE(‘Calendar'[Year]),
CALCULATE([YTDSumOfSales],
SAMEPERIODLASTYEAR(‘Calendar'[Date]))
),
BLANK()
)
hum, All these TOTALYTD(), DATESYTD() Not works for fiscal year, any work arround? Thanks.
You just need to add an optional argument to “YTD” function. For example:
=TotalYTD([measure],’Date Table'[Dates]) will work based on calendar year.
=TotalYTD([measure],’Date Table'[Dates],”06-30″) – will work based on Fiscal Year where is last FY day is June 30th (July – June)
https://msdn.microsoft.com/en-us/library/ee634400.aspx
Excellent article, Johann. Its the best resource I’ve found to help me create some year-over-year reports for giving with regards to fundraising. I’ve gotten everything to work down to the “YTDSumOfSales” step. My “right hand side” chart has no data in it. My YTDSumOfSales measure is blank. Any ideas what might cause that?
Also, my month prefixes in the left-hand chart are sorting alphabetically rather than chronologically. Any ideas there?
Here’s my sum of gifts measure: SumOfGifts = SUM(All_Gifts_Since_2010[Gf_Amount])
Here myYTD sum of gifts measure: SumGiftsCalYTD = totalytd(sum(All_Gifts_Since_2010[Gf_Amount]), ‘Calendar'[Date])
I took it to the next step to create a fiscal year to date, but am getting the same issue. The measure has a blank value:
SumGiftsFYTD = totalytd([SumOfGifts], ‘Calendar'[Date], all(‘Calendar'[Date]), “06/30”)
Ah. Figured out the month name sorting part of this.
Hi Casey,
Glad you got it sorted. Here is a post dealing with this:
http://databear.com/2016/04/23/power-bi-tips-sort-by-month-name/
Did you get your DAX measures to work?
I did sort out the month sort thing, but I haven’t gotten the DAX measures to work. I can’t imagine what’s wrong. I feel like I’ve tried everything.
For TOTALYTD(), does the measure input have to a column or measure? I feel like I tried both. What I my amount input, donation amounts in dollars in this case, has a rouge line with a blank or non-number value or something? I can sum and average that column in other places, though, so that doesn’t make sense. I also tried adding an ALL() filter and explicitly stating the year end date but to no avail.
If you have any ideas I’d be very appreciative of your thoughts.
The possible cause can be in calendar table. By default, the YTD measure will be calculated for the last year in calendar table. Perhaps, the last year in the calendar table is greater than last year in the fact table. If you cannot troubleshoot this by checking the calendar table, and its relationship etc. I suggest providing me with the workbook and I will fix this for you.
That’s exactly what it was! I had my Calendar table going out to the end of 2018 to future-proof my report. Once I change it to end this year (2016) my whole report started working correctly. I played around trying to find a way to have the Calendar table always extend out to the end of the current year and found this, which I think works:
Calendar = Calendar(“2010-01-01″, YEAR(TODAY())&”-12-31″)
Any risks here I don’t know about?
Thank you so much for your help.
I don’t see any risks. This schema will work, but in case you have many different reports, as it is more flexible and easier to replicate, using a Power Query calendar will be better.
I will write a blog about how to do this. :)
“I don’t see any risks. This schema will work, but in case you have many different reports, as it is more flexible and easier to replicate, using a Power Query calendar will be better.
I will write a blog about how to do this. ?”
Awesome. I look forward to it.
I just found your example report here: https://app.powerbi.com/view?r=eyJrIjoiZGYxZTZkMzQtMzUyYi00MWYwLTkyOGEtYzIzYWUwNjIyODlmIiwidCI6ImUwOTY3NDNiLWMyM2QtNGVjOS1hZjI4LWViMGY5OTgxMTcwNCIsImMiOjh9
The YoY comparison on the right side of report page 4 (going back more than one year) is exactly what I’ve been trying to accomplish. I even was trying to use actual year values in the legend rather than labels like “This year” and “last year”. What’s the best way to do that? This is one of the approaches I’m looking at:
Prior year: CALCULATE([Total Sales], DATEADD(DATESYTD(Calendar[Date]),-1,Year))
2 years prior: CALCULATE([Total Sales], DATEADD(DATESYTD(Calendar[Date]),-2,Year))
etc.
You can achieve this only one measure: Sales YTD = TotalYTD([Total Sales],’Calendar'[Date]). And add ‘Year’ column as a legend, ‘Month’ as an axis.
That’s giving me the individual totals for each month, but not a running total from one month to the next throughout the year. Is there something I’m missing?
Actually, if I remove the custom year-end date and don’t specify a customer year-end, it works. Compare:
SumGiftsFYTD = TOTALYTD([SumOfGifts], ‘Calendar'[Date], “06/30”)
to
SumGiftsCalYTD = TOTALYTD([SumOfGifts], ‘Calendar'[Date])
I figured out one of the things that was confusing me with my YTD results. When I use a calculation to sum for the previous YTD, the result is showing me the total at the end of the current period (month) year-to-date, not the day. For example, if I want the sum of previous YTD for Nov 10, the result is for Nov 31, not Nov 10.
How could I create a previous YTD (fiscal year) total that compares by day instead of by period?
It seems it may have to due with my date table. I used CALENDARAUTO() in this report. It seems I should have used your Power Query date table so that the date table ends on the same day as the data. Is there a way I can trim by date table without creating a new date table from scratch?
This is what makes me think that:
https://www.powerpivotpro.com/2016/01/year-to-date-in-previousprior-year/
“Let’s say you’re halfway through a given month – for example, pretend today’s date is July 15, 2016, and I want to know how we’re doing, year to date, versus the same time period of 2015.
In that case, I don’t want ALL of July 2015 included in the comparison! That would be “unfair” to 2016, because I’d have less time in 2016 than the comparison period in 2015.
But if my Calendar table contains all of the dates for July 2016 – it goes slightly into the future and already has rows for July 16, 17, and so on – well guess what? The formula above WILL include the entirety of July 2015 in the comparison.
I’ve never liked this about the built-in date intelligence functions, and this is why I always prefer Calendar tables to be “trimmed” – meaning, the latest date included in the Calendar table matches the latest date for which I have real data (like Sales transactions for instance). A trimmed calendar avoids this problem, and limits my 2015 comparison period to properly “match” my partial month in 2016.”
I don’t know of a way to trim a calendar table without re-creating from a scratch. However, you can try this approach:
Measure PY YTD = TOTALYTD([Measure PY],’Date Table'[Date],’Date Table'[Date]<=TODAY())
Hi Johann,
Thank you for your article, I have a question regarding How to restrict visualization on current year, but I need a dynamic filter so in 2016 the end user has no option to see past years data.
In 2017 the filter works automatically to filter data only for 2017.
Thanks
There’re 2 options to restrict visualization on current year:
1) Add a calculated column to your calendar table (if you don’t have any you can easily create it with DAX or in power query):
IsCurrentYear = IF(‘Date'[Year]=YEAR(TODAY()), 1, 0)
it will dynamically (each time when data is refreshing) show the value 1 only for the current year,
after that you can put this column as a visual, page or report level filter.
See an example here: http://databear.com/wp-content/uploads/2016/12/image0001.png
2) Use a filter in each measure that is put on visual, for example,
Sales Current Year = CALCULATE([Sales],’Date'[Year]=YEAR(TODAY()))
This article is really helpful. I have one query. I am using TotalMTD function to calculate monthly total. If a apply a slicer on month, the behavior of the function changes. My data has mainly three columns,
Project Month Value TotalMTDMEASURE
A Jan2016 10 60
B JAN2016 20 60
C JAN2016 30 60
A FEB2016 40 150
B FEB2016 50 150
C FEB2016 60 150
….. and so on till DEC2016
There are multiple projects. The function works fantastic with Project slicer but when I try to apply slicer on month it group the monthly total by project and I dont get a consolidated value. I will get measure value same as “Value”
Any suggestions please?
Will need to take a look at the data model to pinpoint the issue. Could be some kind of unwanted filtering going on.
Hi Everyone,
I am facing a problem I have created YTD measures in DAX, I want YTD start from April to March instead of January to December
Please help me
As per a previous comment:
You just need to add an optional argument to “YTD” function. For example:
=TotalYTD([measure],’Date Table'[Dates]) will work based on calendar year.
=TotalYTD([measure],’Date Table'[Dates],”06-30″) – will work based on Fiscal Year where is last FY day is June 30th (July – June)
https://msdn.microsoft.com/en-us/library/ee634400.aspx
HI Johan,
Can you please help me out the below DAX function for TOTALMTD.
I have a TOTAYTD to starting with 12-019(Dec – Nov)..this work fine.
Samething i applied for TOTALMTD..but its not working.
Can you please help me how to write a DAX function for TOTALMTD for (Dec-Nov)
Thanks,
Kapil
Hi Kapil,
When calculating MTD, you don’t need to specify the end of the fiscal year. You mentioned “12-019”, I’m unsure of this date format. Are you saying that your your fiscal Year/Month starts in the middle of the calendar month?
Hi Johann,
I am facing issue calculating the YTD using TotalYTD function as in my fact table i don’t have the date as we are calculating monthly data, so fact table contains month number and calendar year, I have created a intermediate table to join this to my date dim based on the month year calculation, so now I have 3 table sin powerBi model, Month year, dim date , fact table.
when I am trying it calculate the YTD basically (year to month ) I am not getting the correct values.
Can you please guide me on the same, how can I calculate the total sum based on month and year i.e year to month, year to month Last year.
Hi Niyati,
Using the Power BI Query editor, you can create the required date field in the date dim tab. Select the Month Year field -> Add New Column -> Date -> Parse
Hi Johann,
How to calculate year to end (YTE) value? Any leads would be appreciated!