top of page
Writer's pictureRobert J Engstrom

DAX: Most common expressions

These are some of the most commonly used DAX expressions used in Power BI. Make a comment if you think something is missing!



Total Sales Calculation

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


Total Cost Calculation

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


Profit Calculator

Profit = [Total Sales]-[Total Cost]


Profit Margin

Profit Margin = DIVIDE([Profit],[Total Sales])


Transaction Count (count rows)

Transactions = COUNTROWS('Table')


Related Table Count (total rows in a related table, ex total transactions by Product)

Transactions + COUNTROWS(RELATEDTABLE('TABLE'))


Related - count of sales in the US

Count of sales orders in the US = COUNTROWS(FILTER(ALL('Sales'). RELATED('SalesGeography'[Countries]) = "United Sates"))


YTD/QTD/MTD Sales

Total Sales this Year = TOTALYTD(SUM('Sales'[Sales]), 'Dates'[Dates])

Total Sales this Quarter = TOTALQTD(SUM('Sales'[Sales]), 'Dates'[Dates])

Total Sales tis Month = TOTALMTD(SUM('Sales'[Sales]), 'Dates'[Dates])


MTD Sales

MTD Sales = TOTALMD([Total Sales], 'Date Table'[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])

)

)


YTD Sales

YTD Sales = CALCULATE (

[Total Sales],

FILTER(

ALL('DateTable'),

'DateTable'[DateYear] = MAX('DateTable'[DateYear])&&

'DateTable'[Date] <= MAX('DateTable'[Date])

)

)


Prior Year Profit

Prior Year Profit = CALCULATE([Profit],SAMEPERIODLASTYEAR'DateTable'[DateColumn])

Prior Year Profit (Direct Query)

Prior Year Profit = CALCULATE(

[Profit],

FILTER(

ALL('DateTable'),

'DateTable'[Year] = MAX('DateTable'[Year]) -1

)

)


Year over Year Profit

YoY Profit = [Profit]-[Prior Year Profit]


Last YTD Sales

Last YTD Sales = CALCULATE([YTD Sales], SAMEPERIODLASTYEAR('DateTable'[DateColumn]))


Total Sales for all Countries

Total Sales All Countries = CALCULATE([Total Sales], ALL('Geography Table'[Country]))


Percent of Total Calculation

Percent of Total = DIVIDE([Total Sales], [Total Sales All Countries])


Rolling 12 Month Sales/Profit

Rolling 12 Month Profit = CALCULATE([Profit].

DATESBETWEEN('DateTable'[DateColumn],

NEXTDAY(

SAMEPERIODLASTYEAR(

LASTDATE('DateTable'[DateColumn]))),

LASTDATE('DateTable'[DateColumn])))


7 Day Moving Average Profit (number of days can be changed)

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)

(example is measure Country ranked by measure [Total Sales])

Country Rank = RANKX(ALL('GeographyTable'[Country]),[Total Sales])


Count of sales orders over 100

Count of sales orders over 100 = COUNTROWS(FILTER('Sales','Sales'[Sales]>100))


ALL (ignoring any filters applied)

ALL(<table> or <column>)


Count of all sales orders, removing filters

Count of all sales orders = COUNTROWS(ALL('Sales'))


Calculate with filter

Sum of sales all countries = CALCULATE(SUM('Sales'[Sales]), ALL('SalesGeography'))


Concatinate String

= [ChannelLabel] & " - " &[ChannelName]

= CONCATENATE(CONCATENATE( [ChannelLabel, " - "), [ChannelName]


Converts date type to string

= FORMAT([Datekey], "YYYY-MM-DD")

YY = 19 ,YYYY = 2019

MM = 06, MMM = Jun, MMMM = June

DD = 07, DDD = Mon, DDDD = Monday


Create Date & DateTime type fields

= DATE(2019,6,1)

= DATE(2019,6,1) + TIME(20,15,0)


DAX Example for new Date Table:

Calendar =

VAR _calendar =

CALENDAR ( "1/1/2011", "12/31/2019" )

RETURN

ADDCOLUMNS (

_calendar,

"Year", YEAR ( [Date] ),

"MonthNumber", MONTH ( [Date] ),

"Month", FORMAT ( [Date], "mmmm" ),

"Quarter", "QTR " & FORMAT ( [Date], "Q" ),

"QuaterNumber", FORMAT ( [Date], "Q" ),

"MonthYearNumber", FORMAT([Date], "yy mm"),

"Month Year", FORMAT([Date], "mmm yyyy")


93 views0 comments

Commentaires


bottom of page