How to Calculate Year to Date in Power BI?

Do you know how to calculate year to date in Power BI? We can use the TOTALYTD() function to calculate year-to-date measures in Power BI.

I was recently working on a Power BI report. Where, with the help of the TOTALYTD() function, calculates the year-to-date total based on a specified expression.

In this Power BI tutorial, we will learn about a year-to-date measure in Power BI and how to create one. Additionally, we’ll explore how to utilize Power BI’s quick measure feature to create a year-to-date calculation.

What is the year-to-date measure in Power BI?

In simple terms, a Year to Date (YTD) measure in Power BI is a calculation that shows the total amount or value accumulated from the beginning of the current year up to the selected date. It helps analyze data within a specific time frame, from the start of the year to the present.

For example, if you have sales data and create a YTD measure for sales, it will show the total sales amount from January 1st of the current year to the selected date.

A simple method to employ a Year to Date in Power BI is utilizing the TOTALYTD function in DAX.

Power BI TOTALYTD function syntax:

TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])

Where:

  • Expression = The expression you want to calculate the year-to-date total for.
  • Dates = The column containing the dates to use for the calculation.
  • Filter = Additional filters you want to apply to the calculation.
  • year_end_date = The date that defines the year-end date.

How to Calculate Year to Date in Power BI

Now, we see how to calculate year-to-date measures in Power BI.

Example:

You work for a retail company that sells products online. Your company wants to analyze the year-to-date (YTD) sales performance to understand how sales have been trending since the beginning of the year.

We can use TOTALYTD() to get results in this business scenario.

Here, we have a SharePoint list (US Superstore data) that contains below columns with various data types:

ColumnsData Types
Order DateDate and time
CitySingle line of text
CategorySingle line of text
Sub-CategorySingle line of text
Product NameSingle line of text
SalesCurrency
QuantityNumber
ProfitCurrency
How to Calculate Ytd in Power BI

Follow the below steps to calculate year-to-date based on sales:

1. Open Power BI Desktop and load the data. Then, you see data in the Data pane.

Power BI Tips Calculate Year-to-date Values

2. Now we create total sales measure. To do this, navigate to the Home tab and click ‘New measure.’

How to Create YTD Calculations in Power BI

3. In the formula bar, click the below expression. Then click Commit.

Total Sales = SUM('US Superstore data'[Sales])

Where:

  • Total Sales = Defines a new measure named Total Sales.
  • SUM = This DAX function calculates the total sum of the values in a column.
  • US Superstore data'[Sales] = Specifies the column from which to calculate the sum, in this case, the Sales column from the US Superstore data table.
How to Calculate Year to Date and Prior YTD in Power BI

4. Now, we create a year-to-date measure. To do this, navigate to the Home tab and click ‘New measure.’

How to Create YTD Calculations in Power BI

5. In the formula bar, click the below expression. Then click Commit.

Year to Date Sales = 
TOTALYTD(
		[Total Sales],
		'US Superstore data'[Order Date]
	)

Where:

  • Year to Date Sales = Defines a new Year to Date Sales measure that calculates total sales.
  • TOTALYTD() = A DAX function calculates a year-to-date total for a given expression.
  • [Total Sales] = The measure represents the total sales amount aggregated to calculate the year-to-date sales.
  • ‘US Superstore data'[Order Date] = Specifies the column containing the order dates, which is used to determine the time frame for the year-to-date calculation.
year to date power bi measure

6. Under the Home tab, expand Visual gallery (black box) -> Click the Table visual.

How to Add a Year-to-Date Running Total in Microsoft Power BI

7. Then, use the +Add data option to add the Order Date, Sales, Year to Date Sales into the Columns field.

year to date measure power bi

8. After that, you can observe the trend of sales from the beginning of the year to the end. In my case, it starts 01-01-2017 to 10-12-2017. Check the screenshot below.

How to display sales YTD and sales last YTD over years in Power BI

This way, we can calculate year-to-date measure Power BI.

Year-To-Date Total Calculation in Power BI Quick Measure

Now we see how to calculate year-to-date in Power BI quick measure.

1. Under the Home tab, click Quick Measure.

year to date quick measure power bi

2. Then a Quick measure pane opens, expand the Select a calculation. Select the Year-to-date total.

power bi quick measure year to date

3. Use the +Add data option to add the Base value as Total Sales and Date as Order Date. Then, click Add a button.

power bi quick measure date difference

4. Then you see the Year-to-date total (Quick measure) added in the Data pane.

When selected (Total Sales YTD), you can observe the DAX expression in the formula bar automatically generated.

Total Sales YTD = 
IF(
	ISFILTERED('US Superstore data'[Order Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	TOTALYTD([Total Sales], 'US Superstore data'[Order Date].[Date])
)
year to date in power bi quick measure

5. Under the Home tab, expand Visual gallery(black box) -> Click the Table visual.

How to Add a Year-to-Date Running Total in Microsoft Power BI

6. Then, use the +Add data option to add the Order Date, Sales, and Total Sales YTD(Quick measure) into the Columns field.

Power BI Quick Measure Calculation for Year to Date

7. After that, you can see the same result we created in the above Power BI table visual.

Power BI Quick Measure Year-To-Date Total Calculation

This way, you can calculate year-to-date in Power BI quick measure.

Conclusion

I hope this article provides all the information on calculating year-to-date in Power BI.

In this tutorial, we covered the year-to-date measure in Power BI, how to calculate year-to-date measures manually in Power BI, and how to quickly calculate year-to-date in Power BI using quick measures.

Also, you may like some more Power BI articles: