DAX Cheat Sheet

Problem Calculation Expression
Total Sales Calculation

Calculated measure using SUM to aggregate a column.

Total Sales = SUM('TableName'[SalesAmount])

Total Cost Calculation

Calculated measure using SUM to aggregate a column.

Total Cost = SUM('TableName'[Cost])

Profit Calculation

Calculated measure using two previously created calculated measures to determine profit.

Profit = [Total Sales] - [Total Cost]

Profit Margin

Calculated measure using two previously created calculated measures to determine profit margin, the DIVIDE function is used to perform the division.

Profit Margin = DIVIDE( [Profit], [Total Sales])

Transaction Count

Calculated measure that returns a count of all rows in a table, ultimately, many times this simple calculation is used to return transaction counts.

Transactions = COUNTROWS('Table')

Related Table Count

Returns the total rows in a related table. For example, total transactions by Product.

Transactions = COUNTROWS(RELATEDTABLE('TABLE'))

Month To Date Sales
Problem Calculation Expression
MTD Sales

Calculates Total Sales for all days in the current month up to the maximum day in the selection.

MTD Sales = TOTALMTD( [Total Sales], 'DateTable'[DateColumn] )

MTD Sales (Direct Query)

Calculates Total Sales for all days in the current month up to the maximum day in the selection.

MTD Sales =
CALCULATE (
[Total Sales],
FILTER (
ALL ( 'DateTable' ),
'DateTable'[DateYear] = MAX ( 'DateTable'[DateYear] ) &&
'DateTable'[DateMonth] = MAX ( 'DateTable'[DateMonth] ) &&
'DateTable'[Date] <= MAX ( 'DateTable'[Date] )
)
)

Year To Date Sales
Problem Calculation Expression
YTD Sales

Calculates Total Sales for all days in the year up to the maximum day in the selection.

YTD Sales = TOTALYTD( [Total Sales], 'DateTable'[DateColumn] )

YTD Sales (Fiscal Calendar)

This calculation uses an optional third parameter specifying the fiscal year end date.

YTD Sales = TOTALYTD( [Total Sales], 'DateTable'[DateColumn], "05/31" )

YTD Sales (Direct Query)

Calculates Total Sales for all days in the year up to the maximum day in the selection.

YTD Sales: =
CALCULATE (
[Total Sales],
FILTER (
ALL ( 'DateTable' ),
'DateTable'[DateYear] = MAX ( 'DateTable'[DateYear] ) &&
'DateTable'[Date] <= MAX ( 'DateTable'[Date] )
)
)

Prior Year Sales
Problem Calculation Expression
Prior Year Profit

Prior Year Profit = CALCULATE ( [Profit], SAMEPERIODLASTYEAR'DateTable'[DateColumn] )

Prior Year Profit (Direct Query)

Calculates Profit for all days in the Year prior to the last year in the selection. Limited to the last day of the selection.

Prior Year Profit =
CALCULATE (
[Profit],
FILTER (
ALL ( 'DateTable' ),
'DateTable'[Year] = MAX ( 'DateTable'[Year] ) - 1
)
)

Year over Year Profit

Calculated measure using two previously created calculated measures to determine YoY profit.

YoY Profit = [Profit] - [Prior Year Profit]

Last Year YTD Sales

Last YTD Sales = CALCULATE ( [YTD Sales], SAMEPERIODLASTYEAR('DateTable'[DateColumn] ) )

Total Sales for all Countries

This calculation uses calculate to return all countries in the calculation regardless of the filter context.

Total Sales All Countries = CALCULATE ( [Total Sales], ALL('Geography Table'[Country] ) )

Percent of Total Calculation

This calculation uses two measures previously created to create a percent of total calculation.

Percent of Total = DIVIDE([Total Sales], [Total Sales All Countries])

Moving Totals
Problem Calculation Expression
Rolling 12 Month Sales

Calculated measure that returns a rolling 12 months total for Profit.

Rolling 12 Months Profit =
CALCULATE ( [Profit],
DATESBETWEEN('DateTable'[DateColumn] ,
NEXTDAY(
SAMEPERIODLASTYEAR(
LASTDATE(DateTable'[DateColumn] ))),
LASTDATE('DateTable'[DateColumn])))

7 Day Moving Average Profit

This calculation generates the daily moving average. The number of days can be changed accordingly.

7 Day Moving Average =
AVERAGEX (
FILTER (
ALL ( 'DateTable' ),
'DateTable'[FullDateAlternateKey] > ( MAX ('DateTable'[FullDateAlternateKey] ) - 7 ) &&
'DateTable'[FullDateAlternateKey] > <= MAX ('DateTable'[FullDateAlternateKey] )
),
[Profit])

Country Rank

Calculated measure to rank a specific column in a table by a measure. In this measure Country from the geography table is being ranked by the measure [Total Sales].

Country Rank = RANKX( ALL ('GeographyTable'[Country]), [Total Sales],,,Skip)