fbpx

NEED HELP? TALK TO AN EXPERT (904) 638-5743

Sign In
Start Learning

DAX Cheat Sheet For Beginners

Getting started with This DAX Guide.

DAX Bootcamps
1:1 Expert Help

Quick DAX References

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)

Get Our Special Offers Directly to Your Email