Getting Better at DAX: Having Fun with Running Totals

This is a fun little tutorial that will give you another chance to practice you CALCULATE() and FILTER() functions skills.

Running totals are a common problem that we have to deal with. To illustrate this problem, I created a sales dataset for innovation products. In our example, after the product reaches $1m in global sales we can no longer consider it as a global innovation product. At the same time, at a market level we also have a similar limitation, a product becomes a mature product after it exceeds $300,000 in sales in the given market. We will create two extra columns in our data set and use a running total approach to classify our sales as innovation or not.

Go head and download the desktop file first and follow along or watch the video and then try to implement my calculations later.

You can download the tutorial files here.

These are the DAX calculations used in the tutorial:

IsGlobalInnovation =
var CurrentProduct = Sales[Product]
var CurrentDate = Sales[Date]
var LifeToDateSales =
CALCULATE(SUM(Sales[Sales]),
FILTER(ALL(Sales),
Sales[Product] = CurrentProduct && Sales[Date]<=CurrentDate
)
)
return IF(LifeToDateSales>1000000, “N”, “Y”)

IsMarketInnovation =
var CurrentProduct = Sales[Product]
var CurrentDate = Sales[Date]
var CurrentMarket = Sales[Market]
var LifeToDateSales =
CALCULATE(SUM(Sales[Sales]),
FILTER(ALL(Sales),
Sales[Product] = CurrentProduct &&
Sales[Market] = CurrentMarket &&
Sales[Date]<=CurrentDate
)
)
return IF(LifeToDateSales>300000, “N”, “Y”)

Life To Date Sales =
CALCULATE(SUM(Sales[Sales]),
FILTER(ALL(Sales[Date]),
Sales[Date]<=MAX(Sales[Date])
)
)

Global Innovation Sales (Life-To-Date) =
CALCULATE([Life To Date Sales],
Sales[IsGlobalInnovation] = “Y”
)

Market Innovation Sales (Life-To-Date) = CALCULATE([Life To Date Sales], Sales[IsMarketInnovation] = “Y”)