Recently, we published a blog covering how to create Year to Date (YTD) calculations in Tableau, which you can find here. Regardless of what industry or company you work in, comparing current year metric performance to previous years is a universal ask. Why? Well, it gives you context for how your business or organization is performing to a relative benchmark, aka, how you’ve performed in the past.
Luckily, Power BI has a lot of built-in functions for handling these calculations, called Time Intelligence functions. We will explore some of these functions as well as cover how to handle some more unique situations. Let’s dive in!
Things to Consider
Before setting up your Year to Date (YTD) and Prior Year to Date (PYTD) calculations there are a few basic questions you should ask. Every dataset is different and every reporting need is different, so let’s dive into these questions first to figure out what we’re working with.
Data Structure - What is the basic structure of your data? What does each row of this data represent. If each row in your fact table represents one transaction or event, and the date corresponds to that event occuring, then you're in luck. Make sure the date field you're looking at isn't what time the data was entered or updated. Data like appointments can have scheduled dates into the future, make sure to understand how all of your date fields function within your data set.
Timing - How often is the data refreshed? Data that is refreshed once a week means that you need to look for max dates or end of week dates rather than just using a TODAY() function. Data that is refreshed twice a day means you will need to look for a max timestamp and not just a day in order to not duplicate your data. Make sure to understand the data refresh schedule.
Reporting Structure - What is the required timeframe structure for your KPIs? If leadership only wants to see your YTD numbers through the end of the previous week, you aren't going to want to include your data for the most recent day if it doesn't fall in under that criteria, even if the data is refreshed daily and in an appropriate structure.
Metric Definition - What is the defined time period that we are reporting on? I have seen my fair share of clients try and report on a weekly basis only to realize that the number of days in their weeks are off from year to year or that
There are most likely going to be other factors to consider before setting up your YTD calculations, but these are a few that I’ve found come up repeatedly with clients. Now let’s start setting the foundation for your calculations.
YTD Using TOTALYTD()
The first Time Intelligence function that we need to evaluate is the TOTALYTD function. This function will total up an expression using a specified date field that you can apply a filter to and specify the year ending date. This last part can be especially useful when calculating fiscal year totals. Here is the expression syntax
//TOTALYTD
(Expression,
[dates],
filters,
year ending date,
)
This syntax is broken down into four distinct parts.
- Expression – an aggregation of a field values such as SUM(Sales_fct[SalesAmt]) or any other aggregations using SUMX, CALCULATE, etc.
- Dates – the dates that will be used to determine year to date. Make sure to choose the dates that correspond to the metric that you are trying to calculate. For instance, use Sales Date if trying to calculate YTD Sales instead of Last Updated Date, for instance.
- Filters – this filter field allows for any criteria or context to be specified when the calculation takes place. This could be to specify a specific region or filter out extraneous data.
- Year Ending Date – this will default to 12/31, but if you want to change this for a fiscal date or other date situation, enter in the date as “mm/dd” using quotations.
Here is an example of a YTD calculation.
//Sales YTD =
TOTALYTD(
SUM(Orders[Sales]),
Orders[OrderDate]
)
Using the filter function you can specify either a more specific field value or time frame. In this example I’ve specified that I only want YTD Sales for the East Region.
//East Region Sales YTD =
TOTALYTD(
SUM(Orders[Sales]),
Orders[Order_Date],
Orders[Region]="East"
)
To get fiscal calendar, use the Year Ending Date option at the end. In this example below, the fiscal year ends on 6/30.
//East Region Sales FYTD =
TOTALYTD(
SUM(Orders[Sales]),
Orders[Order_Date],
Orders[Region]="East",
"6/30"
)
This example below shows a YTD calculation that is not affected by any filters on your report. This can be useful when calculating % of totals that require denominators of calculations to stay constant.
//Sales YTD =
TOTALYTD(
SUM(Orders[Sales]),
Orders[Order_Date],
ALL(Orders)
)
Lastly, this example shows that when you have future dated data in your data set, applying a filter for previous to TODAY() can help eliminate unapplicable data.
//Sales YTD before Today =
TOTALYTD(
SUM(Orders[Sales]),
Orders[Order_Date],
Orders[Order_Date] < TODAY()
)
Using the TOTALYTD function is a good way to start learning Time Intelligence functions in Power BI. Let’s move on to how to calculate PYTD totals.
PYTD using SAMEPERIODLASTYEAR
Now that we’ve learned how to calculate YTD, let’s look at how to calculate the previous year’s totals. Again, Power BI has a function to help us out called SAMEPERIODLASTYEAR. This function takes the same period we are looking at in YTD and applies to the previous year. Here is the syntax below for the PYTD calculation.
//Sales PYTD =
TOTALYTD(
SUM(Orders[Sales]),
SAMEPERIODLASTYEAR(Orders[Order_Date])
)
You can see that everything looks very similar to the YTD calculation except for the Dates section being wrapped in SAMEPERIODLASTYEAR. This function will use the same dates that the TOTALYTD function used to find YTD, but it will apply them to the year before.
This approach will work when you do not have any future data within your data model. In the case that there is, we have to switch up our approach to calculating the PYTD.
First, create a calculated column that says your Dates field is less than Today. In my example I named my column Order Date < Today(). The DAX is below.
//Order Date < TODAY() =
Orders[Order_Date] < TODAY()
Since we have future dated data in our model, the standard TOTALYTD and DATESYTD will take those future dates into consideration when doing YTD. Since that is the case, we need to figure out a way to limit our data table to only the relevant dates. To do this, we are going to use the CALCULATE function and then the CALCULATETABLE function to eliminate our dates that are in the future. See the syntax below:
//Sales PYTD =
CALCULATE(
SUM(Orders[Sales]),
CALCULATETABLE(
SAMEPERIODLASTYEAR(
DATESYTD(Orders[Order_Date])),
Orders[Order Dates < Today])
)
Using the CALCULATETABLE function we are able to specify the exact dates that are to be used to evaluate the DATESYTD function. In this case we are stating DATESYTD(Orders[Order_Date]) and wrapping that in SAMEPERIODLASTYEAR to return the PYTD dates, but in the filter expression we are able to make sure that we limit the table being evaluated to all Dates that are less than today. This way, the original DATEYTD calculation is not picking up those future orders.
You can find an article on a similar approach to this latter solution from the folks at SQLBI, here.
YTD Using Dates Table
Although we can perform most of our YTD and PYTD calculations using DAX, one of the easiest ways to perform these calculations is with the help of a dates table. You can check out my blog post on creating YTD flags using Power Query here.
By creating columns that house YTD, PYTD, Fiscal YTD, and Fiscal PYTD flags, you can easily connect these fields to your DAX calculations. In the example below I have created my Dates Table in Power Query and am using YTD flags to calculate YTD.
//Sales YTD (Flag) =
CALCULATE(
SUM(Orders[Sales]),
'Dates Table (M)'[YTD Flag]
)
Because my Dates Table YTD Flag field is a Boolean (T|F), I can just put that field in there and it is read as TRUE. This means you don’t have to put = TRUE in your calculation. You can then do a similar calculation for your PYTD values.
Here is some M code that our team uses to create Dates Tables in Power Query.
//let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])),
InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),
AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
AddFY
in
fnDateTable
YTD Without Date Field
Another common use case for reporting is having your data come into your dataset with no set dates field. Often times you’ll find that you have a Year field and a week, month, or quarter field. There are a couple of ways we can approach this in Power BI.
The first way to do this is to convert those date columns into date fields using the DATE field. Getting your date columns into a Date format allows you to use your native Time Intelligence DAX functions.
The second approach is to keep the fields separated and use the CALCULATE function to identify the max year/week/month, etc.
//Sales YTD Week Field =
VAR max_year =
CALCULATE(
MAX(Orders[Order Date Year]),
ALL(Orders))
RETURN
CALCULATE(
SUM(Orders[Sales]),
Orders[Order Date Year]=max_year
)
If your data has future data in it that should not be included, you can add an extra statement to your filter:
//Sales YTD Week Field =
VAR max_year =
CALCULATE(
MAX(Orders[Order Date Year]),
ALL(Orders))
RETURN
CALCULATE(
SUM(Orders[Sales]),
Orders[Order Date Year]=max_year
&& Orders[Order_Date] < TODAY()
)
In order to find the PYTD field we want to isolate the same week numbers being used for the current year and apply those to the previous year calculation. You can see in my syntax below that I use a variable to declare what the max year is, but then I subtract 1 to get the previous year. I then declare a variable that finds the max week number for the max year that is less than TODAY() (eliminating the future date issue). I then find the Order Date Year that is equal to my previous_year variable AND my Order Date Weeks that are less than or equal to the max_week from the current year.
//Sales PYTD Week Field =
VAR max_year =
CALCULATE(
MAX(Orders[Order Date Year]),
ALL(Orders))
VAR max_week =
CALCULATE(
MAX(Orders[Order Date Week]),
Orders[Order Date Year]=max_year
&& Orders[Order_Date] < TODAY()
)
VAR previous_year = max_year-1
RETURN
CALCULATE(
SUM(Orders[Sales]),
Orders[Order Date Year]=previous_year
&& Orders[Order Date Week]<= max_week
)
There are endless scenarios like this that analysts have to solve for, but hopefully this example gives you an idea of how to calculate YTD and PYTD without date fields.
Utilizing Different Date Fields
If you have more than one date field in your fact table, you might want to utilize both of those dates fields in your calculations. As you can see in the data model below, my Orders fact table has both an Order Date and a Ship Date. My active relationship is currently between the Date and Order_Date fields with an inactive relationship between the Ship Date (fct) and Date (dates table). If you are wanting to utilize a different Dates relationship other than the one that is currently active, you will need to use the USERELATIONSHIP command.
If you are using your Dates Table for your YTD dates field, then you can use syntax similar to the calculation below.
//Sales YTD Ship Dates =
CALCULATE(
SUM(Orders[Sales]),
USERELATIONSHIP(Dates[Date],Orders[Ship_Date]),
DATESYTD(Dates[Date])
)
If you’re not running your YTD calculations through your dates table, you can simply switch the dates that are being used in your TOTALYTD calculation. See how we’ve calculated YTD based on Ship Date instead of Order Date below.
//Sales YTD Ship Date =
TOTALYTD(
SUM(Orders[Sales]),
Orders[Ship_Date]
)
YTD as of Most Recently Completed Month
Lots of reporting is completed at month, quarter, or year end, so this use case is very common with clients. Lots of times the story of your data is not useful just a few days into a month or quarter. Business and processes are cyclical, so reporting as of the most recently completed timeframe helps control for some of the variability.
As we’ve shown, there are a variety of ways to solve the same problem, so this will just be one example of how to accomplish this calculation. First, let’s create a calculated column that returns the end of the previous month for each order date. This can be accomplished using the EOMONTH function. This column will not be used in our final calculation, but it’s good to see how the function works before we use it.
//End of Previous Month =
EOMONTH(Orders[Order_Date],-1)
You can see in the screenshot below how the dates are returned using this function. As an example, if your Order Date was 1/20/2020, then the End of Previous Month column will read 12/31/2019.
Now that we know how this function works, it’s fairly easy to implement in our YTD calculations. First, we use a variable to declare the max date in our data set. In order to eliminate future dated data you could also filter the Orders table for Order Dates < Today(). Then, we use our normal TOTALYTD syntax except for when we get to the filter section we filter out all Order Dates that are less than the previous month end of our max date. See how we use the EOMONTH function with our declared max date variable in the syntax below
//Sales YTD (Most Recent Month End) =
VAR max_date =
CALCULATE(
MAX(Orders[Order_Date]),ALL(Orders))
RETURN
TOTALYTD(
SUM(Orders[Sales]),
Orders[Order_Date],
Orders[Order_Date] <= EOMONTH(max_date,-1)
)
//Sales PYTD (Most Recent Month End) =
VAR max_date =
CALCULATE(
MAX(Orders[Order_Date]),ALL(Orders))
RETURN
TOTALYTD(
SUM(Orders[Sales]),
SAMEPERIODLASTYEAR(Orders[Order_Date]),
Orders[Order_Date] <= EOMONTH(max_date,-1)
)
Finally, if you have future dated data in your data model and want to take those dates out of your calculation, then you can add in the Order Dates < Today field that we created in an earlier section.
//Sales YTD (Most Recent Month End) Before Today =
VAR max_date =
CALCULATE(
MAX(Orders[Order_Date]),
FILTER(ALL(Orders),Orders[Order Dates < Today]))
RETURN
TOTALYTD(
SUM(Orders[Sales]),
Orders[Order_Date],
Orders[Order_Date] <= EOMONTH(max_date,-1)
)
You can see the differences in the calculations in the table below. (Today equals 5/23/2021 in the table below)
In Conclusion
Now that we’ve created all of these YTD and PYTD calculations you can use the DIVIDE function to create your YoY change measures.
// Sales YTD v PYTD
DIVIDE([Sales YTD]-[Sales PYTD], [Sales PYTD])
As you can see, there are a variety of ways to handle these YTD calculations in Power BI. While the native Time Intelligence functions are very useful, you can use a variety of other methods to handle your specific use case as well. Hopefully this blog has given you an idea of how to tackle YTD and PYTD calculations in Power BI!
