Calculating Year to Date (YTD) and Prior Year to Date (PTYD) in Power BI are two essential skills for building dashboards. You can use Power BI DAX formulas to perform the calculations and add them to various visuals to compare results over time.
To calculate YTD and Prior YTD in Power BI you can using a combination of the TOTALYTD and SAMEPERIODLASTYEAR DAX formulas. When combined with the CALCULATE function the formulas can be mixed and matched within a dashboard across different time periods and slicers.
Let’s take a look at when to calculate YTD in Power BI and some different use cases.
Table of Contents
Why Calculate YTD using DAX Formulas in Power BI?
YTD calculations are typically used as a way to compare results over time. A year is a standard measure of time when comparing performance and typically represents a full business cycle. Many companies will set annual budgets and sales metrics that they work towards achieving for the full period.
DAX Measures in Power BI are a way to dynamically perform calculations. Whereas Power Query is designed for data transformations. Most calculations within Power Query are performed one column at a time at the row level. DAX allows you to create calculations that are at an aggregate level.
Because we want to aggregate values at a YTD level that’s dynamic and changes as new dates are added, we want to use DAX formulas as part of the report building functions in Power BI.
Some additional benefits of using DAX in Power BI for YTD and PYTD calculations are:
- Dynamic Updating – As new data is loaded into Power BI, DAX formulas automatically recalculate. Calculations are also impacted by filtering and slicers if report viewers want to look at current YTD vs previous YTD performance.
- Customization – We’ll look at YTD functions that start at the beginning of a calendar year, but DAX formulas can be filtered for any data range, any starting point or end point.
- Reusability – One of the best parts of Power BI is that once a measure is created, it can be used and re-used in combination with different filters to create once and use many times.
These are just some of the benefits of using DAX for YTD calculations in Power BI. The other huge benefit is that creating YTD calculations is much easier in DAX than trying to setup a running total at the row level in the Power Query Editor.
The Importance of Date Tables in Power BI
Power BI requires the use of a Date Table when working with time intelligence. Time intelligence is the name for any calculation that occurs over a period of time. It could be YTD, or it could be something like a comparison of month over month change.
In many cases, date data in not sequential when it’s imported into Power BI. There are often dates missing and some days will have no activity. Because of the gaps in time, Power BI will need you to add a relationship to the data that consists of a sequence of dates.
Check out our guide to creating date tables to learn more. It’s a great skill to have and will help you avoid many of the potential pitfalls of dates not being in order along with many other errors.
While Power BI does create a date table automatically in many instances it isn’t sufficient and it’s a better practice to create your own and relate it.
The Importance of Date Data Types in Power BI
Another important step to avoiding potential errors when creating DAX formulas in Power BI is to ensure that the columns you are using are defined as a date data type. Data types are what Power BI uses to determine whether to treat a column as text, numbers, or dates.
For example, Power BI would know how to add two columns of numbers together. It could concatenate or add two columns of text together, but it will get confused if you try to add numbers and text.
The fastest way to determine the current data type of a column is to look at the icon on the left side of the field name in the Data tab. A calendar will notate that it’s a date data type.
To change the data type of a column in Power BI, you can right click the column name in the Power Query Editor, select Change Type, and assign the new data type to it.
Once you have columns assigned to the correct data types, and have established a related date table you will avoid most errors that occur when working with YTD and Previous YTD DAX functions.
How to Calculate YTD totals in Power BI using TOTALYTD DAX Functions
The fastest way to calculate a YTD total in Power BI is to use the TOTALYTD DAX formula. There are two parameters that you have to define. You have to give the formula the column that you want to add, along with the date column being used.
The formula will look like the following:
YTD Receivables = TOTALYTD(SUM(Receivables[Dollar Amount]), DateTable[Date])To use the formula, create a New Measure in Power BI by clicking on the New Measure button.
This allows you to type in the full formula, assigning the field that needs to be aggregated on a YTD basis along with the date column from the date table being used.
Note: It may not always be necessary to create a date table in Power BI, it will usually generate one but because it doesn’t always seem to work the way we expect it to we use a CALENDAR function to create one to avoid potential issues when building DAX date formulas.
The following example shows the full setup of the TOTALYTD DAX formula along with the field assignments and what it looks like inside of a Matrix visual.
Try updating the Matrix visual to “Show Values on Rows” to see two rows of the starting amount and the YTD amount next to each other. It makes it very easy to eyeball if the YTD calculation is working as expected. Normally the total dollar amount field will be grouped by month.
Another trick you will see in this screenshot is the use of a Power BI measure table. It’s a separate empty table that’s used to store calculations. It helps keep your calculations clean and outside of your base data model.
Calculate a Previous YTD in Power BI Using SAMEPERIODLASTYEAR
To calculate the previous year to date in Power BI, you use the same calculation as TOTALYTD but modify it to use the same period last year as a modifier to the formula. We’ll walk through several examples and highlight the pieces of the formula but first we’ll start with the finished product for context.
In this example, we use the previous YTD calculation. You can reference previous measures in a DAX formula. It’s a super helpful technique to breakdown calculations into smaller easier to manage steps.
PYTD Receivables = CALCULATE([YTD Receivables],SAMEPERIODLASTYEAR(DateTable[Date]))We could have also written the formula out as:
PYTD Receivables = CALCULATE(TOTALYTD(SUM(Receivables[Dollar Amount]), DateTable[Date]),SAMEPERIODLASTYEAR(DateTable[Date]))Both formulas do the same thing, but we’ll reference the first version for simplicity sake.
You’ll notice that there are two changes to our initial TOTALYTD calculation.
The CALCULATE and SAMEPERIODLASTYEAR functions.
What does the CALCULATE Function do in Power BI DAX?
The CALCULATE function in DAX allows you to modify the context of a formula. It lets you ignore the standard filtering criteria for calculations and override them. By telling Power BI to CALCULATE you are telling it to diverge from the standard procedure and then you give it another path to follow for filtering.
CALCULATE is often used with different FILTER criteria if you want to create a specific measure that isn’t impacted by slicers and filters that are on the page. For instance, running totals, month over month calculations, MTD, or any other filter that differs from the other filtered values being presented.
Modifying a YTD Calculation with SAMEPERIODLASTYEAR
Returning to our example, we tell Power BI to ignore the normal filtering that occurs to aggregate a YTD total by using the CALCULATE formula.
PYTD Receivables = CALCULATE([YTD Receivables],SAMEPERIODLASTYEAR(DateTable[Date]))Then the second part of the formula says to use the SAMEPERIODLASTYEAR based on the Date Table to filter the formula instead.
Any time you are applying a filter to a formula in Power BI or really most times you are adding onto a formula, Power BI will ask you to separate the sections with a comma.
The final Prior Year to Date formula in Power BI DAX will look like this:
As you can see, you can quickly calculate a YTD formula and then modify it to be a second measures that calculates prior YTD.
Calculating Power BI YTD Totals with Quick Measures
If you hate working with DAX and writing formulas, Power BI has a low-code way of letting you drag and drop fields into a template to quickly generate formulas called Quick Measures. Quick Measures exist for a number of common calculations including YTD.
To use Quick Measures navigate to Home and click Quick Measures. A new panel will open up and you can drag and drop fields into the template. The DAX formula will be automatically generated and you click the check box to accept it.
An even faster way to generate DAX formulas in Power BI is to use the Suggestions with Copilot button under Quick Measures. It utilizes Natural Language Processing, such as the technology behind ChatGPT to convert plain English into a measure.
There are several pre-requisites to enabling and using suggestions with Copilot which we cover here. It’s worth a read and can be a big time saver if you’re new to writing DAX.
Even though this feature uses advanced AI to generate DAX formulas it is not always accurate. Having a basic knowledge of common formulas such as TOTALYTD will save time in the long run and prevent you from having to troubleshoot bad AI generated measures.
Conclusion
YTD and Prior YTD calculations in Power BI are essential skills to have. DAX makes these calculations even easier by having pre-built functions such as TOTALYTD and SAMEPERIODLASTYEAR which helps developers create these common calculations quickly. Previously, you would have to calculate a minimum and maximum date separately.
To avoid coding altogether, Power BI developers can try using Quick Measures to drag and drop fields to auto-generate common calculations, or used advanced AI features under Power BI Copilot.