Power Pivot Principles: Cumulative Totals with the EARLIER Function
10 September 2019
Welcome back to our Power Pivot blog. Today, we look at another application of the EARLIER function.
Last week we explained how the EARLIER function worked (you can read about it here), and we showed how to use the EARLIER function to create a calculated column that ranks products based on total sale amounts.
This week we’re going to cover another application for the EARLIER function: how to create a cumulative total column in Power Pivot.
In this example, we will be using the following dataset (this image is not exhaustive):
After loading the data to our data model, we may use the EARLIER function to create a cumulative total column. For example, say we wish to write a DAX formula that can calculate the cumulative amount sold for each product, ascending with the price. The formula would look like this:
=CALCULATE(
SUM([Amount Sold]),
FILTER('SaleTbl',
SaleTbl[Price] <= EARLIER(SaleTbl[Price])
)
)
This formula uses the same logic that we covered in the previous blog regarding the inner and outer row context. To avoid repeating ourselves, we will only go over it briefly here:
- The FILTER function is evaluating the Price for each row for the entire table, inner row context
- The function then has to evaluate ‘EARLIER(SaleTbl[Price])’. The EARLIER function instructs the function to evaluate the outer row context which is just ‘SaleTbl[Price]’. On the first row, this evaluates to ‘$0.00’, which is the price of the first row
- The FILTER function then returns with a table with all of the rows that have a price that is greater than or equal to ‘$0.00’
- The CALCULATE function then sums the Amount Sold for all of these rows which is incidentally 43 (in the first row)
- Steps 1 to 4 are repeated for the rest of the rows in the table.
If you are still confused as to how this works, we’d recommend reading our previous blog as go over how the EARLIER function works in greater detail.
Let’s see if the EARLIER function works with dates too.
We will be using the following dataset:
We can use the following DAX code to create a calculated column:
=CALCULATE(
SUM(SaleDataDates[Total Sales]),
FILTER('SaleDataDates',
SaleDataDates[Date] <= EARLIER(SaleDataDates[Date])
)
)
The EARLIER function works with dates too!
That’s it for this week, come back next week for more Power Pivot. Until then, happy pivoting!
Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about >here. If you wish to catch up on past articles in the meantime, you can find all of our past Power Pivot blogs here.
