Power BI Tips: Calculating Year To Date Values

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.

ytd1

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.

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?

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.

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). Modelling 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’, 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.

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 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:

ytd6

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:

ytd7

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!

By | 2016-10-20T22:52:58+00:00 May 8th, 2016|Microsoft Power BI, Power BI reports, Power BI Tips|28 Comments

About the Author:

Johann is the founder of Data Bear and specializes in getting the right data into the right hands, in a format that is quick and easy to understand. Revolutionizing your reporting experience from a time consuming frustration into dynamic interactive visualizations is what he takes pleasure in.

28 Comments

  1. Lee August 25, 2016 at 11:23 am - Reply

    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?

    • Johann September 9, 2016 at 7:29 pm - Reply

      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()
      )

  2. fbilo September 19, 2016 at 10:57 am - Reply

    hum, All these TOTALYTD(), DATESYTD() Not works for fiscal year, any work arround? Thanks.

    • Johann September 29, 2016 at 9:24 am - Reply

      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

  3. Casey Hancock October 18, 2016 at 8:11 pm - Reply

    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”)

  4. Johann October 20, 2016 at 1:35 pm - Reply

    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?

    • Casey Hancock October 20, 2016 at 2:04 pm - Reply

      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.

      • Johann October 20, 2016 at 7:08 pm - Reply

        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.

        • Casey Hancock October 24, 2016 at 12:27 pm - Reply

          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.

          • Johann October 24, 2016 at 2:40 pm

            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. 🙂

          • Casey Hancock October 24, 2016 at 6:12 pm

            “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.

  5. Casey Hancock October 26, 2016 at 1:00 pm - Reply

    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.

  6. Johann November 1, 2016 at 7:37 am - Reply

    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.

    • Casey Hancock November 1, 2016 at 5:54 pm - Reply

      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])

  7. Casey Hancock November 10, 2016 at 4:33 pm - Reply

    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?

  8. Casey Hancock November 10, 2016 at 8:10 pm - Reply

    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.”

  9. Johann November 16, 2016 at 3:04 pm - Reply

    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())

  10. Mohamed Ali Hefnawy December 8, 2016 at 10:44 pm - Reply

    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

    • Johann December 23, 2016 at 12:02 pm - Reply

      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()))

  11. Isha December 13, 2016 at 3:11 am - Reply

    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?

    • Johann December 23, 2016 at 10:21 am - Reply

      Will need to take a look at the data model to pinpoint the issue. Could be some kind of unwanted filtering going on.

  12. Sunil February 8, 2017 at 11:26 am - Reply

    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

    • Johann February 8, 2017 at 5:14 pm - Reply

      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

      • Kapil Dev Pallepati September 8, 2017 at 2:23 pm - Reply

        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

        • Johann September 9, 2017 at 10:43 am - Reply

          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?

  13. Niyati May 18, 2017 at 1:37 pm - Reply

    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.

    • Johann May 30, 2017 at 9:46 pm - Reply

      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

Leave A Comment