DAX formula to calculate the YTD value of a Power BI measure.
Power BI tutorial on how to calculate the YTD value using a DAX formula. To create a YTD type measure using Power BI, use the TOTALYTD time function. YTD is the abbreviation of the Year-To-Date and it represents the total to date of a given measure. In other words, from the beginning of the year to the current date. The YTD value can be based on a fiscal calendar or on a regular Gregorian calendar.
Indeed, to calculate the year-to-date sales total with a DAX formula, use the built-in function called TOTALYTD. From this example, easily copy and paste the code and adjust it to the data analysis and reporting requirements.
Table of Contents
1. Guide to create a YTD value with Power BI Desktop
Below are 6 steps to create a YTD Calculation in Power BI:
- Launch Power BI Desktop and start a new report.
- In the Fields pane, pick the desired table/dataset.
- Navigate to the Modeling tab and click on New Measure.
- Then, in the Power BI Formula Bar, input this formula : YTD Sales = TOTALYTD( SUM(sales[sales]), ‘time'[date_key], REMOVEFILTERS (sales))
- Hit Enter on the keyboard.
- Your YTD Sales calculation is now ready to use in the Power BI reporting section.
2. Calculate the Year-To-Date value with a DAX formula
Indeed, this example calculates the YTD total of the sales amount using a simple DAX function. The TOTALYTD DAX function needs a time dimension to work. To achieve this, flag one dimension as time. In the DAX example below, we use the ‘time'[date_key] column.
YTD Sales =
TOTALYTD(
SUM(sales[sales]),
'time'[date_key],
REMOVEFILTERS (sales)
)
The result is the report section of Power BI Desktop is the Sales and the Sales YTD columns, and the second shows the total of the current year, up to the month. So in the screenshot below the Total for 2018 is the same as the YTD total for December 2018.
3. Use the REMOVEFILTERS function to filter Year-To-Date results
The REMOVEFILTERS function in DAX is used to clear filters that might be applied to a table or column, essentially restoring the original, unfiltered data set for a specific calculation. When used with time-intelligence functions like TOTALYTD, not employing REMOVEFILTERS can lead to incorrect results. This is because the TOTALYTD function computes the cumulative total from the start of the year to the current period, but if filters are applied, it might not consider the entire range.
For instance, if a filter restricts data to a specific month, without REMOVEFILTERS, TOTALYTD would calculate the year-to-date total just for that month, overlooking previous months. By using REMOVEFILTERS, you ensure that the cumulative calculation encompasses the whole year’s data, regardless of any filters applied elsewhere in your report or visualization.
Conclusion on YTD calculations with Power BI
That is all for this short tutorial on how to calculate the YTD value in Power BI using built-in DAX functions in a simple formula. This related article explains what DAX abbreviation means and how the language is used in Microsoft tools. To go further and get some more insights on DAX functions, check out the Microsoft Power BI website. This short article explains how to calculate the year-to-date number of sales for the selected fiscal year or calendar year. Check another tutorial to learn how to calculate the last year value in DAX.
FAQ on Year to date values in Power BI
YTD stands for Year-To-Date, it is a business planning metric tracking progress over a year. It’s commonly used in finance to assess sales or revenue from the start of the year to the present day. The YTD metric often employs a fiscal calendar tailored to a business’s specific needs, like tax or budgeting timelines.
YTD and YTG are both cumulative time calculations, but they represent different periods of time. YTD (Year-To-Date) covers a company’s performance from the year’s start to now. It tracks progress and informs decisions. YTG (Year-To-Go) projects performance from now to year’s end, aiding in forecasting. Together, YTD + YTG give a full year’s view, helping companies assess performance and plan resources.
