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”)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s