# DAX Workout 027 - Essential functions to transform your data

Title: DAX 101: Essential functions to transform your data

Description:

DAX, or Data Analysis Expressions, is the formula language behind Power BI’s powerful analytical capabilities. In this workout, you’ll explore the basics of DAX, understanding its syntax and diving into some essential functions to transform your data analysis in Power BI.

Scenario:

You’re analyzing sales data in Power BI and want to derive some custom metrics. For instance, you wish to calculate the average sales per product, the growth rate month-on-month, and a rolling average for the last three months. How can DAX assist you in creating these custom metrics?

Objectives:

By the end of this workout, you should be able to:

1. Understand the core principles and syntax of DAX.

2. Create basic calculated columns and measures using DAX.

3. Recognize common DAX functions and their applications in data analysis.

Given your beginner’s perspective on DAX, answer the following:

1. If you want to calculate the total sales for a particular product category, how would you frame your DAX formula?

• Your Approach: ________________________
2. How would you use DAX to calculate the month-on-month growth rate in sales?

• Your Approach: ________________________
3. To create a rolling average of sales for the last three months, which DAX function(s) might you consider using?

Questions:

1. In DAX, what is the fundamental difference between a calculated column and a measure?

• i) Calculated columns use data, while measures use formulas.

• ii) Calculated columns are created at the row level, whereas measures are aggregated.

• iii) Calculated columns can only be used in tables, while measures can be used in charts.

• iv) There’s no difference; they can be used interchangeably.

2. Which DAX function would you use to calculate the sum of a particular column in your dataset?

• i) SUMMARIZE()

• iii) TOTAL()

• iv) SUM()

Duration: 20 minutes

Difficulty: Beginner

Period:
This workout is released on Tuesday, October 10, 2023, and will end on Friday, October 20, 2023. But you can always come back to any of the workouts and solve them.

Solution to this Workout as follows:

Questions:

1. In DAX, what is the fundamental difference between a calculated column and a measure?
• ii) Calculated columns are created at the row level, whereas measures are aggregated.
1. Which DAX function would you use to calculate the sum of a particular column in your dataset?

• iv) SUM()

1. If you want to calculate the total sales for a particular product category, how would you frame your DAX formula?
• Your Approach: ____
Total Sales for Electronics =
SUMX(
FILTER(Sales, Sales[ProductCategory] = “Electronics”),
Sales[SalesAmount]
)
1. How would you use DAX to calculate the month-on-month growth rate in sales?

Approach:

1. First, make sure you have a date table in your data model. If you don’t have one, create a date table with a date column that links to your sales data.
2. Create a measure that calculates the total sales for the current month. You can use the SUM() function for this. Assuming you have a “Sales” table with a “SalesAmount” column and a date table with a “Date” column, the formula would be:

DAXCopy code

``````Total Sales This Month = SUM(Sales[SalesAmount])
``````
1. Create another measure to calculate the total sales for the previous month. You can use the SAMEPERIODLASTYEAR() function to get the sales for the same month in the previous year and then use CALCULATE() to sum the sales for that month. Here’s the formula:

DAXCopy code

``````Total Sales Last Month =
CALCULATE(
[Total Sales This Month],
SAMEPERIODLASTYEAR('Date'[Date])
)
``````
1. Calculate the month-on-month growth rate by subtracting the total sales for the previous month from the total sales for the current month and dividing by the total sales for the previous month. Here’s the formula:

DAXCopy code

``````MoM Sales Growth Rate =
DIVIDE(
[Total Sales This Month] - [Total Sales Last Month],
[Total Sales Last Month],
0
)
``````

This formula calculates the growth rate, and the `DIVIDE()` function ensures that you handle potential divide-by-zero errors by setting the third argument to 0.

Now, you can use the “MoM Sales Growth Rate” measure in your Power BI reports or any other DAX-compatible tool to display the month-on-month sales growth rate.

1. To create a rolling average of sales for the last three months, which DAX function(s) might you consider using?

Assuming you have a table named “Sales” with columns “Date” and “SalesAmount,” and you want to calculate the rolling average of sales for the last three months:

DAXCopy code

``````Rolling Average 3 Months =
AVERAGEX(
FILTER(
Sales,
Sales[Date] >= MAX(Sales[Date]) - 83 && Sales[Date] <= MAX(Sales[Date])
),
Sales[SalesAmount]
)
``````

In this formula:

1. `FILTER(Sales, Sales[Date] >= MAX(Sales[Date]) - 83 && Sales[Date] <= MAX(Sales[Date])` filters the “Sales” table to include only the rows where the “Date” falls within the last three months. The `MAX(Sales[Date])` function is used to determine the latest date in the dataset.
2. `AVERAGEX()` calculates the average of the “SalesAmount” for the filtered rows, giving you the rolling average of sales for the last three months.

This formula considers the dynamic nature of the last three months, so as time progresses, the rolling average will automatically update to include the most recent data.

Thanks for the workout.

Keith

Hi @EnterpriseDNA,
1.
Total Sales = SUM(‘Sales’[Total Revenue])
Total sales Croissant =
CALCULATE([Total Sales],
FILTER(‘Product Lookup’,‘Product Lookup’[product] = “Croissant”))

Total Sales LM =
CALCULATE(
[Total Sales],
‘Date’[Date] , -1 , MONTH
)
)
MoM Growth Rate % =
DIVIDE(
[Total Sales] - [Total Sales LM],[Total Sales LM])

Rolling 3 Month Avg =
CALCULATE([Total Sales],
DATESINPERIOD(‘Date’[Date],MAX(‘Date’[Date]), -3,MONTH))

Question 1:
ii) Calculated columns are created at the row level, whereas measures are aggregated

Question 2:
iv) SUM()

1 Like

Basic Measure
Sales = SUM(‘Sales’[Total Revenue])

1.TotaSalesCroissant = CALCULATE([Sales], ‘Product Lookup’[product] = “Croissant”))

2.MoMSalesGrowthRate =
Var_PMSales=CALCULATE(
[Sales],DATEADD(‘Date’[Date] , -1 , MONTH)
)
Var _CMSales =[Sales]
Return
DIVIDE(_CMSales- _PMSales],_PMSales)

Rolling 3 Month Avg =
Var _CurrentDate = MAX(‘Date’[Date])
Var _Rolling3 Month = DATESINPERIOD(‘Date’[Date],_CurrentDate , -3,MONTH)
Return
CALCULATE([Sales],_Rolling3 Month)

1. If you want to calculate the total sales for a particular product category, how would you frame your DAX formula?

calculate(sum(total sales), product cat = " ")

1. How would you use DAX to calculate the month-on-month growth rate in sales?

VAR CurrentMonthSales = CALCULATE( SUM(Sales[Total Sales]), DATESMTD(‘Date’[Date]) )
VAR PreviousMonthSales = CALCULATE( SUM(Sales[Total Sales]), DATESMTD( DATEADD(‘Date’[Date], -1, MONTH) ) )
RETURN DIVIDE(CurrentMonthSales - PreviousMonthSales, PreviousMonthSales)

1. To create a rolling average of sales for the last three months, which DAX function(s) might you consider using?

AVERAGEX(
DATESINPERIOD(
‘Date’[Date],
LASTDATE(‘Date’[Date]),
-3,
MONTH
),
CALCULATE(SUM(Sales[Total Sales]))
)

Questions:

1. In DAX, what is the fundamental difference between a calculated column and a measure?
• ii) Calculated columns are created at the row level, whereas measures are aggregated.
1. Which DAX function would you use to calculate the sum of a particular column in your dataset?
• iv) SUM()