Answers for "dax formulas"

dax formulas

Calculated measure using SUM to aggregate a column

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

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

		Calculated measure using two previously created calculated 
    				measures to determine profit
                    
Profit = [Total Sales] - [Total Cost]

		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])

		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')

		Returns the total rows in a related table. For example, total 
        				transactions by Product.
                        
Transactions = COUNTROWS(RELATEDTABLE('TABLE')) 

		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) = MTD Sales = 
    CALCULATE (
          [Total Sales],
          FILTER (
               ALL ( 'DateTable' ),
                    'DateTable'[DateYear] = MAX ( 'DateTable'[DateYear] ) &&
                    'DateTable'[DateMonth] = MAX ( 'DateTable'[DateMonth] ) &&
                    'DateTable'[Date] <= MAX ( 'DateTable'[Date] ) 
       )
    )
    
  		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 (Direct Query) = 
    CALCULATE (
          [Total Sales],
          FILTER (
               ALL ( 'DateTable' ),
                    'DateTable'[DateYear] = MAX ( 'DateTable'[DateYear] ) &&
                    'DateTable'[Date] <= MAX ( 'DateTable'[Date] ) 
       )
    )
  
		This calculation uses an optional third parameter specifying 
        				the fiscal year end date.
                        
YTD Sales (Fiscal Calendar) = TOTALYTD( 
										[Total Sales], 
                                        'DateTable'[DateColumn], 
                                        "05/31" 
                              )

dax formulas

Prior Year Profit = CALCULATE( 
								[Profit], 
								SAMEPERIODLASTYEAR('DateTable'[DateColumn])
                             )
                             
       	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 (Direct Query) = 
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 YTD Sales = CALCULATE ( 	[YTD Sales], 
								SAMEPERIODLASTYEAR('DateTable'[DateColumn] ) 
                           )
                           
		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] ) 
                                      )
		This calculation uses two measures previously created to 
        		create a percent of total calculation.   
                
Percent of Total = DIVIDE([Total Sales], [Total Sales All Countries])

		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])))
              
		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])
       
		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)