[ad_1]
Calculating a year-to-date total is a common task when tracking income. It’s a type of running total—a continually adjusting total that adds and subtracts values as they occur. For instance, 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 scour the internet for the 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 a year-to-date total to a simple dataset in Power BI. They’re both 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 it encounters the new year.
I’m using Microsoft Power BI on a Windows 10 64-bit system with a simple .pbix demonstration file that you can download. If you want to start from scratch, you can download the .xlsx file that contains the 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 data set in Power BI
For demonstration purposes, we’ll work with a simple dataset that contains a column of unique dates. You can work with your own data if you prefer, but the date values must be unique.
Figure A shows the relationship between the facts table and a custom date table that I’ve marked as a date table.
Figure A
Figure B shows the function in Listing A 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 through 2022. The date column in the facts table contains dates for the years 2021 and 2022, so it isn’t necessary to include 2020. You must accommodate the 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 might want to read How to know if the Auto date table is adequate 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 let’s suppose you’re asked to add a running total to the simple table visualization shown in Figure C. You might try to create 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 pane 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.
5. Expand the Sales table (to the right), and add the Amount field to the Base Value bucket.
6. Add the SalesDate field to the Field bucket (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 pane. To see the DAX code, click the formula bar’s dropdown arrow. As you can see, the new column adds the current value to the previous total for every record.
Figure E
The quick measure is much easier to implement than the code, so let’s take a minute 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 pane and choosing Rename.
- The SUM function evaluates the Amount field in the Sales table, which you specified when creating the quick measure.
- The FILTER function might be a bit of a surprise, but it’s the ISONORAFTER function that does the heavy lifting by specifying the current value and all those above.
There’s certainly a lot more going on than the simple Excel expressions you’d use. That’s why I recommend checking quick measures before trying to write the 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 encounters a “new” date, it will reset to 0 and start over. It’s similar to a running total, but it’s a series of running totals rather than one running total. Fortunately, it’s just as easy to create as the running total:
1. Click the Sales table in the Fields pane 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 Year-To-Date Total from the Calculation dropdown.
5. Expand the Sales table (to 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). Notice that the returned values are the same as those in the running totals column until the date 2/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 measure’s default name.
- The second line uses the TOTALYTD function to calculate the Amount values by the year.
I recommend you always check what quick measures are available before you try to write the DAX code yourself. You might be surprised at how much they can do.
[ad_2]
Source link
