Get the YTD of the same period last year

I was looking at my blog stats and found that some of the most popular posts are almost 10 years old (yes really). Those topics are actually still relevant so I will be updating some those for Power BI in the coming months. Maybe not everything has changed but it will be a bit different with Power BI.

So we will start by reexamining a blog post I did on September 22, 2010 to “Get the YTD of same period last year using DAX”. In this blog post we examine working with with YTD and the different options.

We start with a very simple table of data with Sales over 2 years:

On of the prerequisites of working with dates is to have a date table. Power BI automatically creates one for us when we load in this table. You can recognize this by the date icon on my date field and the fact it can be expanded to show a date hierarchy.

In general we do not want to use this build in table, it cannot be extended and doesn’t scale well when we have multiple tables later on. Instead we will build our own. I created a new calculated table called Calendar with the the DAX function “CALENDARAUTO“. This function automatically figures out what the min and max date is in the model. It then uses those dates to generates a single column with all the dates in between. This will give you a contiguous date range that you need to use DAX time intelligence functions. Next I use some DAX calculated columns to add Year and Month.

Now that we created the table all we need to do is add a relationship between both tables. After we created this the build in date table will disappear on the Sales table.

One thing you can notice is that now the date column in the Calendar table has the date icon. We also don’t want that. What we can do is tell the Power BI engine this is a date table so it knows we are in control ourselves. You can select the date table and use the “Mark as Date Table” function.

Doing this will give some benefits for the DAX engine, especially if you use surrogate keys ins of real real dates for your relationships. This is common when you use a data warehouse as your source.

Now that we have the model ready lets start. We want to compare the Year to Date from the current year to the YTD of the previous year to the current date last year. I’ll start with a regular matrix that shows the sales by year.

Now I want to get the sales YTD for previous year. I do this by combining two different DAX expressions, TOTALYTD which allows us to get the YTD for a year and DATEADD that allows us to change the current date. It looks like this:

Sales prev YTD =
TOTALYTD ( SUM ( Sales[sales] ), DATEADD ( ‘Calendar'[Date], -12, MONTH ) )

This will get the YTD for the current date as determined by the matrix -12 months (so previous year). So far so good, yeah?

Well maybe.. what happens here is that the DAX engine took the whole date range we have in context and shifts it back 12 months. This means for year 2019 it will use January first to December 31. So we get the entire year, is that what we want? Or do we want to see the sales for the previous year until the day we have data for this year so we can compare? Both need different DAX so let’s take a look.

First we need to get the last date we have sales. We’ll do this with the following measure: LASTDATE(DATEADD(Sales[Date],-12,MONTH))

Here we use the LASTDATE on the Date column in the Sales table to determine last date of the current selected year in the matrix. This gives us “8/8/2019” for the last sales date and then move it back one year to “8/8/2018”. We don’t use the date table as it would give us 12/31/2019.

Next we need to get the first date that we want to use to calculate the sales. For this we will use the FIRSTDATE function. This looks like this: FIRSTDATE(DATEADD(datum[Date],-12,MONTH))

Here we get the FIRSTDATE in the date table for that year, in this case that is
“1/1/2019” and again we move that date 1 year earlier “1/1/2018”. Now all we have to do is use these 2 dates to calculate the sales in that period. We do this with the DATESBETWEEN  function:

Sales sameperiod =
CALCULATE (
    SUM ( Sales[sales] ),
    DATESBETWEEN (
        ‘Calendar'[Date],
        FIRSTDATE ( DATEADD ( ‘Calendar'[Date], -12, MONTH ) ),
        LASTDATE ( DATEADD ( Sales[Date], -12, MONTH ) )
    )
)

This measure will now get us the sales between the 2 dates we found. This gives us the following result:

As you can see we only have sales for part of the previous year. This allows for a much better comparison of the YTD sales. Unfortunately there is one “problem” left, we get the grandtotal as sales for the 2018 previous year. Why? So the problem here is that both the First and last date return empty values in those years. When we put this measure in the visual you can see the problem.

When the DATESBETWEEN function gets 2 empty values it will fall back to the entire date range. We need to make sure we catch this “problem”. Therefore what we will do is check if either of those dates is empty and return BLANK in that case, else we show the YTD. Finally the measure looks like this:

Sales sameperiod =
VAR first_date =
    FIRSTDATE ( DATEADD ( ‘Calendar'[Date], -12, MONTH ) )
VAR last_date =
    LASTDATE ( DATEADD ( Sales[Date], -12, MONTH ) )
RETURN
    IF (
        ISBLANK ( first_date ) || ISBLANK ( last_date ),
        BLANK (),
        CALCULATE (
            SUM ( Sales[sales] ),
            DATESBETWEEN ( ‘Calendar'[Date], first_date, last_date )
        )
    )

Remember folks: use variables as often as you can 🙂 in this case it will definitely improve your performance makes it easier to read.

So this looks good right? Well maybe :). This is the expression I used in my original blog post and sure it works if you just use years but what happens when I add months?

You’ll see that it doesn’t do what we expect it do. The reason for this is that we use LASTDATE and FIRSTDATE, this is now taking the current month as context instead of the beginning of the year. This means we need to use a different technique. Instead we need to use the STARTOFYEAR function that will make sure it gets the start of the current year and subtract 365 days from it to get the start of the previous year. We will still use LASTDATE to get the last date for the Sales table. We can’t use DATEADD as this will give issues with the contiguous data range that is needed to use time intelligence functions, the date column in the sales table contains gaps (not contiguous). So again we will just subtract 365 days to get to the same date one year before.

The measure ends up as such:

Sales sameperiod =
VAR startyear =
    STARTOFYEAR ( ‘Calendar'[Date] ) – 365
VAR enddate =
    LASTDATE ( Sales[Date] ) – 365
RETURN
    CALCULATE (
        SUM ( Sales[sales] ),
        DATESBETWEEN ( ‘Calendar'[Date], startyear, enddate )
    )

If we look at the measures in the matrix we will see it works as expected.

Also interesting to see that we don’t have to check anymore for empty values, this is because we are not longer using DATEADD to go back in time, the simple subtraction will always work.

Hope this blog post gives you some insights in how time intelligence functions work and what you need to keep an eye out for.