---
title: Introduction to DAX - Part 3 (Revision)
tags: dax-foundation-course
---
:::info
DAX materials for Analyzing Data in Power BI course.

[datapot.vn](datapot.vn), 2022.
:::

## Obj 1: Understand Filter Context

### Task 1: Learn how to use Time Intelligence functions

**Requirement**: Calculate accumulated revenue from the beginning of the year.

Renuve YTD = TOTALYTD([Revenue], 'Date'[Date], "6-30")


**Requirement**: Calculate revenue of the same period last year

Revenue PY = 
CALCULATE(
    [Revenue],
    PARALLELPERIOD(
        'Date'[Date], -12, MONTH)
)

Or you can use the following formula:

Revenue PY (other) = 
// Use SAMEPERIODLASTYEAR
CALCULATE(
    [Revenue],
    SAMEPERIODLASTYEAR('Date'[Date]))


**Requirement**: Calculate annual growth rate of revenue.

Sales YoY Growth = 
DIVIDE([Revenue] - [Revenue PY], [Revenue PY])

Create the following visual with filter condition: Revenue is not blank and Sales YoY Growth is not blank.


### Task 2: Learn how to use `REMOVEFILTERS` function

**Requirement**: Calculate total revenue of all region in the world

Revenue All Region =
CALCULATE([Revenue], REMOVEFILTERS(Geography))

**Requirement**: Calculate the percentage revenue of individual region to all region in the world

Revenue % All Region =
DIVIDE(
    [Revenue],
    [Revenue All Region]
)

**Requirement**: Calculate the percentage revenue of individual region to all other regions in a particular country

Revenue % Country = 
DIVIDE(
    [Revenue],
    CALCULATE(
        [Revenue],
        REMOVEFILTERS(Geography[Region])
    )
)

**Requirement**: Calculate the percentage revenue of individual country to all other countries in a group

Revenue % Group =
DIVIDE(
    [Revenue],
    CALCULATE(
        [Revenue],
        REMOVEFILTERS(Geography[Region], Geography[Country])
))

Create the following matrix:


## Obj2: Understand Row Context

### Task 1: Calculate discount amount during a specific period with calculated columns.

**Requirements**: AdventureWorks decided to reduce 15% on list price for Resellers sales. This promotion is applied to all order after 01/01/2020. Create a new column to calculate discount amount for each order item satisfies the promotion.

Discount Amount = 
IF(
    Sales[OrderDate] >= date(2020, 1, 1),
    Sales[Quantity] *    
        RELATED('Product'[ListPrice]) * 0.15, 
    0
)

### Task 2: Calculate discount amount during a specific period with iterator function.

The discount amount calculated column can be written in measure as follows:

Discount = 
CALCULATE(
    SUMX(
        'Sales',
        Sales[Quantity] *    
        RELATED('Product'[ListPrice]) * 0.15),
    Sales[OrderDate] >= date(2020, 1, 1))

Compare calculated columns and measure with interator function:


### Task 3: Calculate average revenue per order after discount

Avg. Revenue per Order = 
AVERAGEX(
    'Sales',
    [Revenue] - [Discount])

Check the measure by creating the following matrix:


**Fix the average measure with `VALUES` function**:

Avg. Revenue per Order (fixed) = 
AVERAGEX(
    VALUES(Sales[SalesOrderNumber]),
    [Revenue] - [Discount])