---
title: DAX formulas
tags: dax
---

# -------------- Calculated tables -------------- #
Ship Date = 'Date' # Then change column names to ship-related date

"""
Date as Ship Date
Fiscal Year as Ship Fiscal Year
Fiscal Quarter as Ship Fiscal Quarter
Month as Ship Month
Full Date as Ship Full Date
"""

"""
Fiscal Hierarchy:
    Ship Fiscal Year
    Ship Fiscal Quarter
    Ship Month
    Ship Full Date
"""

# Create auto date table:
Due Date = CALENDARAUTO(6)

# -------------- Calculated columns -------------- #
# Create the following calculated date table associated with the Due Date table:
Due Fiscal Year =
"FY"
	& YEAR('Due Date'[Due Date])
		+ IF(
			MONTH('Due Date'[Due Date]) > 6,
			1
		)

Due Fiscal Quarter =
'Due Date'[Due Fiscal Year] & " Q"
	& IF(
		MONTH('Due Date'[Due Date]) <= 3,
		3,
		IF(
			MONTH('Due Date'[Due Date]) <= 6,
			4,
			IF(
				MONTH('Due Date'[Due Date]) <= 9,
				1,
				2
			)
		)
	)
    
Due Month = FORMAT('Due Date'[Due Date], "yyyy mmm")
Due Full Date = FORMAT('Due Date'[Due Date], "yyyy mmm, dd")
MonthKey = YEAR('Due Date'[Due Date]) * 100) + MONTH('Due Date'[Due Date])

"Create hierarchy tương tự như với bảng Ship Date"

# To the Sale table, create calculated columns:
Discount Amount =
(
	Sales[Order Quantity]
		* RELATED('Product'[List Price])
) - Sales[Sales Amount]

## Add measure to the data model

# -------------- Measures -------------- #

Quantity = SUM(Sales[Order Quantity])
# Skipped ties argument
Quantity = SUM(Quantity)

Product Quantity Rank =
RANKX(
	ALL('Product'[Product]),
	[Quantity]
)

# Dense ranking
Product Quantity Rank =
RANKX(
	ALL('Product'[Product]),
	[Quantity],
	,
	,
	DENSE
)

Product Quantity Rank =
IF(
	HASONEVALUE('Product'[Product]),
	RANKX(
		ALL('Product'[Product]),
		[Quantity],
		,
		,
		DENSE
	)
)

## DAX Interator functions

Revenue = SUM(Sales[Sales Amount])

Revenue =
SUMX(
	Sales,
	Sales[Order Quantity] * Sales[Unit Price] * 
    (1 - Sales[Unit Price Discount Pct])
)

Discount =
SUMX(
	Sales,
	Sales[Order Quantity]
	* (
		RELATED('Product'[List Price]) - Sales[Unit Price]
	)
)

Revenue Avg =
AVERAGEX(
	Sales,
	Sales[Order Quantity] * Sales[Unit Price] * 
    (1 - Sales[Unit Price Discount Pct])
)

Revenue Avg Order =
AVERAGEX(
	VALUES('Sales Order'[Sales Order]),
	[Revenue]
)

## Modify DAX filter context in Power BI model

Revenue Red = CALCULATE([Revenue], 'Product'[Color] = "Red")
Revenue Red or Green = CALCULATE([Revenue], 'Product'[Color] IN {"Red", "Green"})
Revenue Expensive Products = CALCULATE([Revenue], 'Product'[List Price] > 1000)

Revenue High Margin Products =
CALCULATE(
	[Revenue],
	FILTER(
		'Product',
		'Product'[List Price] > 'Product'[Standard Cost] * 2
	)
)

Revenue Red =
CALCULATE(
	[Revenue],
	FILTER(
		'Product',
		'Product'[Color] = "Red"
	)
)

Revenue Total Region = CALCULATE([Revenue], REMOVEFILTERS('Sales Territory'))

Revenue % Total Region =
VAR CurrentRegionRevenue = [Revenue]
VAR TotalRegionRevenue =
	CALCULATE(
		[Revenue],
		REMOVEFILTERS('Sales Territory')
	)
RETURN
	DIVIDE(
		CurrentRegionRevenue,
		TotalRegionRevenue
	)
    
Revenue % Total Country =
VAR CurrentRegionRevenue = [Revenue]
VAR TotalCountryRevenue =
	CALCULATE(
		[Revenue],
		REMOVEFILTERS('Sales Territory'[Region])
	)
RETURN
	DIVIDE(
		CurrentRegionRevenue,
		TotalCountryRevenue
	)
    
Revenue % Total Group =
VAR CurrentRegionRevenue = [Revenue]
VAR TotalGroupRevenue =
	CALCULATE(
		[Revenue],
		REMOVEFILTERS(
			'Sales Territory'[Region],
			'Sales Territory'[Country]
		)
	)
RETURN
	DIVIDE(
		CurrentRegionRevenue,
		TotalGroupRevenue
	)

Revenue Red =
CALCULATE(
	[Revenue],
	KEEPFILTERS('Product'[Color] = "Red")
)

Revenue Shipped =
CALCULATE (
	[Revenue],
	USERELATIONSHIP('Date'[DateKey], Sales[ShipDateKey])
)

Sales Commission =
[Revenue]
	* IF(
		HASONEVALUE('Sales Territory'[Country]),
		IF(
			VALUES('Sales Territory'[Country]) = "United States",
			0.15,
			0.1
		)
	)
    
Revenue % Total Country =
VAR CurrentRegionRevenue = [Revenue]
VAR TotalCountryRevenue =
	CALCULATE(
		[Revenue],
		REMOVEFILTERS('Sales Territory'[Region])
	)
RETURN
	IF(
		ISINSCOPE('Sales Territory'[Region]),
		DIVIDE(
			CurrentRegionRevenue,
			TotalCountryRevenue
		)
	)
    

## DAX Time Intelligence

Time intelligence calculations modify date filter contexts. They can help you answer these time-related questions:

- What's the accumulation of revenue for the year, quarter, or month?
- What revenue was produced for the same period last year?
- What growth in revenue has been achieved over the same period last year?
- How many new customers made their first order in each month?
- What's the inventory stock on-hand value for the company's products?

To work with time intelligence DAX functions, you need to meet the prerequisite model requirement of having at least one date table in your model. A date table is a table that meets the following requirements:

* It must have a column of data type Date (or date/time), known as the date column.
* The date column must contain unique values.
* The date column must not contain BLANKs.
* The date column must not have any missing dates.
* The date column must span full years. A year isn't necessarily a calendar year (January-December).
* The date table must be indicated as a date table.

**Summarizations over time**
* `DATESYTD` DAX function - Returns a single-column table that contains dates for the year-to-date (YTD) in the current filter context. This group also includes the `DATESMTD` and `DATESQTD` DAX functions for month-to-date (MTD) and quarter-to-date (QTD). You can pass these functions as filters into the `CALCULATE` DAX function.
* `TOTALYTD` DAX function - Evaluates an expression for YTD in the current filter context. The equivalent QTD and MTD DAX functions of `TOTALQTD` and `TOTALMTD` are also included.
* `DATESBETWEEN` DAX function - Returns a table that contains a column of dates that begins with a given start date and continues until a given end date.
* `DATESINPERIOD` DAX function - Returns a table that contains a column of dates that begins with a given start date and continues for the specified number of intervals.

**Comparisons over time**
`DATEADD` DAX function - Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current filter context.

`PARALLELPERIOD` DAX function - Returns a table that contains a column of dates that represents a period that is parallel to the dates in the specified dates column, in the current filter context, with the dates shifted a number of intervals either forward in time or back in time.

`SAMEPERIODLASTYEAR` DAX function - Returns a table that contains a column of dates that are shifted one year back in time from the dates in the specified dates column, in the current filter context.

Many helper DAX functions for navigating backward or forward for specific time periods, all of which returns a table of dates. These helper functions include `NEXTDAY` , `NEXTMONTH` , `NEXTQUARTER` , `NEXTYEAR` , and `PREVIOUSDAY` , `PREVIOUSMONTH` , `PREVIOUSQUARTER` , and `PREVIOUSYEAR` .

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

Revenue PY =
VAR RevenuePriorYear = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
	RevenuePriorYear
    
Revenue YoY % =
VAR RevenuePriorYear = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
	DIVIDE(
		[Revenue] - RevenuePriorYear,
		RevenuePriorYear
	)
    

**Calculate new occurrences**

"""
Calculate the number of new customers for a time period. A new customer is counted in the time period in which they made their first purchase.
"""

Customers LTD =
VAR CustomersLTD =
	CALCULATE(
		DISTINCTCOUNT(Sales[CustomerKey]),
		DATESBETWEEN(
			'Date'[Date],
			BLANK(),
			MAX('Date'[Date])
		),
		'Sales Order'[Channel] = "Internet"
	)
RETURN
	CustomersLTD
    

New Customers =
VAR CustomersLTD =
	CALCULATE(
		DISTINCTCOUNT(Sales[CustomerKey]),
		DATESBETWEEN(
			'Date'[Date],
			BLANK(),
			MAX('Date'[Date])
		),
		'Sales Order'[Channel] = "Internet"
	)
VAR CustomersPrior =
	CALCULATE(
		DISTINCTCOUNT(Sales[CustomerKey]),
		DATESBETWEEN(
			'Date'[Date],
			BLANK(),
			MIN('Date'[Date]) - 1
		),
		'Sales Order'[Channel] = "Internet"
	)
RETURN
	CustomersLTD - CustomersPrior

**Snapshot calculations**


Stock on Hand =
CALCULATE(
	SUM(Inventory[UnitsBalance]),
	LASTDATE('Date'[Date])
)

Stock on Hand =
CALCULATE(
	SUM(Inventory[UnitsBalance]),
	LASTNONBLANK(
		'Date'[Date],
		CALCULATE(SUM(Inventory[UnitsBalance]))
	)
)