<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=612681139262614&amp;ev=PageView&amp;noscript=1">
Skip to content

Need help? Talk to an expert: phone(904) 638-5743

Quick DAX References

Scroll down to view the DAX Cheat Sheet Basic formulas you can use to make changes to control properties, including format, position, simple calculations, and implementing conditional formatting.
Start Here:
DAX Beginner Tutorial Video The best interactive video to start when learning DAX. Learn the DAX fundamentals as well as topics like row context, the CALCULATE function and context transition.
Dashboard In A Day Free Recordings Click Free Trial for a forever free Dashboard in a Day class amongst other courses.
Download Our Free DAX Guidebook Get a breakdown of how you can use DAX to get the most out of your data.
Get Started With Power BI For Free Create rich, interactive reports with visual analytics at your fingertips—for free. Once there, click Try Power BI For Free and use your credentials.
Power BI Licensing See the present pricing for Power BI

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)