Table of Contents
YTD or Year to Date values in Power BI can be calculated by using the DATESYTD function. This function takes two arguments – the date column and the year-end month. It then sums up the values from the date column starting from the beginning of the year till the selected year-end month. This provides a cumulative total for the year so far. This can also be combined with other functions like CALCULATE to create more complex calculations. YTD values are useful in analyzing trends and performance over a specific period of time and can be displayed in visualizations to track progress.
The easiest way to calculate year to date values in Power BI is to use the TOTALYTD function in DAX.
For example, you can use the following syntax in DAX to create a new measure that calculates the sum of the year to date values in the Sales column of a table named my_data:
YTD Sales = TOTALYTD(SUM(my_data[Sales]), my_data[Date])
The following example shows how to use this syntax in practice.
Example: How to Calculate YTD (Year to Date) Values in Power BI
Suppose we have the following table in Power BI named my_data that contains information about sales made on various dates by some company:
Suppose we would like to create a measure that calculates the sum of the year to date values in the Sales column of a table.
To do so, click the Table tools tab along the top ribbon, then click the New measure icon:
Then type the following formula into the formula bar:
YTD Sales = TOTALYTD(SUM(my_data[Sales]), my_data[Date])
This will create a new measure named YTD Sales that contains the sum of the year to date values in the Sales column of the table.
We can then click the Report View icon on the left side of the screen:
Then we can click the Table icon under the Visualizations tab and drag Date, Sales and YTD Sales under the Columns panel:
This will produce the following table that contains the Date, Sales and Year to Date Sales:
From the table, we can see:
- Year to Date Sales up to 1/8/2024 is 10.
- Year to Date Sales up to 1/10/2024 is 24.
- Year to Date Sales up to 1/13/2024 is 54.
And so on.
Note: You can find the complete documentation for the TOTALTYD function in DAX .
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: