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