# DAX Cheat Sheet

ProblemCalculation 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
ProblemCalculation 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
ProblemCalculation 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
ProblemCalculation 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
ProblemCalculation 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)
```
```