by Calculating a year-to-date total is a common task when tracking income. It is a type of running total: a continuously adjusting total that adds and subtracts values as they occur. For example, your checking account balance is a running total of debits and credits as they occur.
A YTD total would return a running total but for a specific year. When you need such totals, don’t search the Internet for Data Analysis Expressions code because quick measures are available for both types of running totals.
In this tutorial, I’ll show you how to add a simple running total and year-to-date total to a simple dataset in Power BI. Both are running totals, but the YTD evaluates dates within the same year. If you have more than one year in your dataset, the measure will start over when you find the new year.
I use Microsoft Power BI on a 64-bit Windows 10 system with a simple .pbix demo file that you can download. If you want to start from scratch, you can download the .xlsx file that contains your data, which you can then import into Power BI.
SEE: Microsoft Power Platform: What you need to know about it (Free PDF) (TechRepublic)
How to prepare the dataset in Power BI
For demonstration purposes, we will be working with a simple dataset that contains a column of unique dates. You can work with your own data if you prefer, but date values must be unique.
Figure A shows the relationship between the fact table and a custom date table that I have marked as a date table.
Figure A
Figure B shows the Listing A function used to create the date table.
Figure B
Listing A
Date =
ADDCOLUMNS (
CALENDAR (DATE (2020, 1, 1), DATE (2022, 12, 31)),
"Year", YEAR([Date]),
"MonthNumber", FORMAT([Date], "MM"),
"Quarter", FORMAT ([Date], "Q" ),
"DayOfWeek", FORMAT ([Date], "dddd" )
)The most important part of the date table is the YEAR function, which specifies the years 2020 to 2022. The date column in the fact table contains dates for the years 2021 and 2022, so you do not need to include 2020. You must accommodate year values in your data for this to work correctly when applying this to your own data.
If you’re not familiar with the date table, you can read How to tell if the automatic date table is appropriate when using Power BI or How to create a date table in Microsoft Power BI.
With the tables and relationship in place, you’re ready to start analyzing the data.
How to calculate a simple running total in Power BI
Now suppose you are asked to add a running total to the straight table visualization shown in Figure C. You can try creating the necessary DAX code yourself, but that’s not necessary because Power BI has a quick measure that will calculate a running total.
Figure C
To add a running total measure to the dataset, do the following:
1. Click the Sales table in the Fields panel to add the measure to this table.
2.Click the Table Tools contextual tab.
3. In the Calculations group, click Quick Measure.
4. In the resulting dialog, choose Running Total from the Calculation dropdown menu.
5. Expand the Sales table (on the right) and add the Amount field to the Base Value bucket.
6. Add the SalesDate field to the Field field (Figure D).
Figure D
7. Click OK.
Power BI adds the quick measure to the Sales table (Figure E). Add the quick measure to the table visualization by checking it in the Field panel. To view the DAX code, click the dropdown arrow in the formula bar. As you can see, the new column adds the current value to the previous total for each record.
Figure E
Quick measure is much easier to implement than code, so let’s take a minute to see how the underlying DAX code works:
- The first line is the default name, which you can change by right-clicking the measure in the Fields panel and choosing Rename.
- The SUM function evaluates the Amount field in the Sales table, which you specified when you created the quick measure.
- The FILTER function may be a bit surprising, but it’s the ISONORAFTER function that does the heavy lifting by specifying the current value and all previous ones.
There is certainly much more at stake than the simple Excel expressions you would use. That’s why I recommend checking quick steps before trying to write DAX code yourself.
Now let’s see what Power BI has to offer in the way of returning a YTD column.
How to calculate a YTD total in Power BI
A YTD total evaluates values with the same date value. When the measure finds a “new” date, it will reset to 0 and start over. It is similar to a running total, but it is a series of running totals instead of a running total. Fortunately, it’s just as easy to create as the running total:
1. Click the Sales table in the Fields panel to add the measure to this table.
2. Click the Table Tools contextual tab.
3. In the Calculations group, click Quick Measure.
4. In the resulting dialog box, choose Total Year To Date from the Calculation drop-down menu.
5. Expand the Sales table (on the right) and add the Amount field to the Base Value bucket.
6. Expand the Date table and add Date to the Field bucket (Figure F).
Figure F
7. Click OK.
Add the new measure, Amount YTD, to the visualization (Figure G). Note that the values returned are the same as the running totals column as of 02/17/22. That’s because the date changed from 2021 to 2022.
Figure G
Interestingly, the DAX code is much simpler this time:
- The first line is the default name of the measure.
- The second line uses the TOTALYTD function to calculate the Amount per Year values.
I recommend that you always check what quick measures are available before attempting to write DAX code yourself. You will be surprised how much they can do.
