Power BI - DAX Functions

DAX (Data Analysis Expressions) is a formula language designed for data calculations, data modeling and analysis in various tools, including Microsoft Power BI, SQL Server Analysis Services SSAS, and Excel Power Pivot. DAX expressions are very similar to Excel formulas but designed for relational data models. There are more than 200 DAX functions that we can use to perform data calculations and analysis. In this article, we will learn about various DAX functions in detail.

Table of Contents

DAX Functions

DAX (Data Analysis Expressions) functions are divided into categories. Some of the important categories are:

Aggregate Functions – Aggregate functions are used for basic mathematical operations. Some of the aggregate functions are SUM, AVERAGE, COUNT, MIN, MAX.
Time Intelligent Functions – Time intelligent functions allow users to perform calculations based on dates and times. Some of the time intelligent functions are DATEADD, TOTALYTD, SAMEPERIODLASTYEAR.
Logical Functions – Logical functions are used to perform logical tests and return values based on conditions. IF, SWITCH are the logical functions.
Filter Functions – Filter functions are used to apply filters to data for calculations. Some of the filter functions are FILTER, ALL, CALCULATE
Text Functions – Text functions are used to manipulate text strings. Some of the text functions are CONCATENATE, LEFT/RIGHT, FORMAT.
Mathematical and Statistical Functions – These mathematical functions are used to perform mathematical operations like ROUND, DIVIDE.
Relationships and Table Functions – These functions work with table relationships and data sets. Some of the relationships and table functions are RELATED, RELATEDTABLE, VALUES.
Rank and Sort Functions – These functions are used to sort and rank data. Some of the rank and sort functions are RANKX, TOPN.

Time Intelligence Functions

Time Intelligence functions in DAX (Data Analysis Expressions) are designed to handle calculations related to dates and time periods. Time intelligence functions support calculations based on the knowledge of calendars and dates. These functions helps in comparing data across different time intervals such as days, weeks, months, quarters, or years. Time intelligence functions are essential for creating reports that deal with day-to-day comparisons, month-to-date analysis, cumulative totals, etc.

We can use meaningful time and date ranges in combination with aggregations or calculations to build meaningful comparisons across comparable periods for sales, inventory, etc. These functions form the basis for the improvement of data analytics by allowing manipulation of data with respect to time periods. Time intelligence functions are very important in carrying out complex calculations, such as year-to-date and quarter-to-date, among many others. Use of time intelligence functions significantly enhance the analytics capability of Power BI.

Date Tables (Calendar Table) in Power BI

In order to use the time intelligence functions in Power BI, we need to mark one of the tables which are used to input the data column as Date Table. This identification of date table is prerequisite for time intelligence functions in Power BI.

A Date Table (also called a Calendar Table) is essential in Power BI when working with Time Intelligence functions. Date table should contain a contiguous range of dates and contain all the possible dates represented within data set. Date Table helps handle calculations and analysis that involve dates, such as year-over-year comparisons, month-to-date analysis, or cumulative totals.

Why need a Date Table

Time Intelligence Functions – Many DAX time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR, etc.) require a proper date table.
Hierarchical Grouping – A date table allows grouping by year, quarter, month, and week.
Custom Time Periods – For fiscal years or custom periods, a date table is a must.

Create Date Table in Power BI

Criteria for Creating a Date Table in Power BI

The date table must contain a date column of datatype date/time.
The date column must not contain any blanks or duplicate values.
The date column should not be missing any date.
The date table must span whole years, i.e it needs to include all date values stored in the date table, be it calendar years (January to December) or fiscal years.
The date table needs to be tagged as a Date Table.

In Power BI, Date Table can be generated manually using DAX formula. Power BI also provides some built-in features to create date table. There are four major ways in which date tables can be generated in Power BI:

Import Date Table from the source Data
Auto Date/Time
Create Date Table using DAX
Create Date Table using Power Query

Create Date Table using DAX

In DAX, we can use CALENDAR and CALENDARAUTO functions to create date tables.

CALENDAR function – CALENDAR function takes two parameters – start date and end date. It returns a range of dates based on the start and end dates specified as function parameters.

Date Table = CALENDAR(DATE(2024, 1, 1), DATE(2034, 12, 31))

CALENDARAUTO function – CALENDARAUTO function shows a range of dates, which are automatically detected from a dataset. The start date would represent the oldest date in the dataset, while the end date is the most recent date in the data set.

Date Table = CALENDARAUTO([fiscal_year_end_month])

Use CALENDAR function in Power BI

Go to the Table tab on the ribbon in Power BI Desktop.
Select New Table, then input the DAX formula
Date Table = CALENDAR (DATE(2024, 1, 1), DATE(2034, 12, 31))

Power BI - DAX - Date Table — **Power BI – DAX – Date Table**

Power BI - CALENDAR formula for Date Table — **Power BI – CALENDAR formula for Date Table**

Add Columns in Date Table

To add new column in Date Table, select the New Column button on the ribbon and input the DAX equation for each column you want to add.

Power BI - Date Table - New Column — **Power BI – Date Table – New Column**

We can add various columns in Date Table.

Year = Year([DATE])
Quarter = "Q" & QUARTER([Date])
Month = FORMAT([Date], "MMMM")
Month Number = MONTH([Date])
Week = WEEKNUM([Date])
Weekday = WEEKDAY([Date], 2)
Any other relevant attributes (like holidays, fiscal year, etc.)

Power BI - Data Table - New Columns — **Power BI – Data Table – New Columns**

DateTable = 
ADDCOLUMNS(
    CALENDAR(DATE(2024, 1, 1), DATE(2034, 12, 31)),
    "Year", YEAR([Date]),
    "Month Number", MONTH([Date]),
    "Month Name", FORMAT([Date], "MMMM"),
    "Quarter", "Q" & QUARTER([Date]),
    "Year-Month", FORMAT([Date], "YYYY-MM"),
    "Week Number", WEEKNUM([Date]),
    "Weekday", WEEKDAY([Date], 2),
    "Weekday Name", FORMAT([Date], "dddd"),
    "IsWorkingDay", IF(WEEKDAY([Date], 2) < 6, TRUE, FALSE),  // Monday to Friday are working days
    "Fiscal Year", IF(MONTH([Date]) > 6, YEAR([Date]) + 1, YEAR([Date])),
    "Fiscal Quarter", "Q" & IF(MONTH([Date]) > 6, QUARTER([Date]), QUARTER([Date]) + 1)
)

Mark as Date Table – Once we create a Date Table, you need to mark it as a date table.

Go to the “Model” view, select your date table, and then in the ribbon, click on “Mark as Date Table” to specify the primary date column.

Power BI - Mark as Date Table — **Power BI – Mark as Date Table**

DAX – Time intelligence functions

DAX in Power BI provides us time intelligence functions. Time intelligence functions in DAX, enables us to modify data by time periods in days, months, quarters, and years, and further construct and compare calculations over different time periods.

DAX Time intelligence functions can be used for

Comparing data over different time frames
Calculating Year-To-Date (YTD), Quarter-To-Date (QTD) and Month-To-Date (MTD) values
Identifying trends, trends, comparing, and forecasting
Building meaningful comparisons across comparable periods for sales, inventory, and more.

Power BI functions efficiently introduce comparison of two time periods by simple calculations rather than going through complex Excel pivot tables. There are several DAX functions that we can use for Time Intelligence in Power BI to do such calculations.

Sales ID	Number of Products	Sales Amount	Order Date
101	33	30000	2024-03-02
102	56	40000	2024-01-23
103	60	37000	2024-02-11
104	10	35000	2024-05-17
105	40	50000	2024-04-08

DAX – Sample Table

TOTALYTD

TOTALYTD stands for Total Year-To-Date. This function calculates the running total from the start of the year up to that date. It is very useful in tracking year-to-date performance.
Syntax => TOTALYTD(expression, date_column, [filter])
Example => SalesYTD = TOTALYTD(SUM(Sales[Sales Amount]), Sales[Order Date])

TOTALQTD

TOTALQTD stands for Total Quarter-to-Date. This function calculates the running total for a quarter up to that date. This function is very useful when working with quarter-to-date analysis.
Syntax => TOTALQTD(expression, date_column, [filter])
Example => SalesQTD = TOTALQTD(SUM(Sales[Sales Amount]), Sales[Order Date])

TOTALMTD

TOTALMTD stands for Total Month to Date. This function gives the cumulative total for the month to that date. It is useful for month-to-date analysis.
Syntax => TOTALMTD(expression, date_column, [filter])
Example => SalesMTD = TOTALMTD(SUM(Sales[Sales Amount]), Sales[Order Date])

SAMEPERIODLASTYEAR

SAMEPERIODLASTYEAR returns a set of dates for the same period in the previous year. This function will be useful when comparing data for the corresponding period in the previous year for year-over-year analysis.
Syntax => SAMEPERIODLASTYEAR(date_column)
Example => Sales Last Year = CALCULATE(SUM(Sales[SalesAmount]), SAMEPERIODLASTYEAR(DimDate[Date]))

PREVIOUSMONTH, PREVIOUSQUARTER, PREVIOUSYEAR

PREVIOUSMONTH returns the previous month
PREVIOUSQUARTER returns the previous quarter
PREVIOUSYEAR returns the previous year
We can use them when comparing the current period values to prior periods.
Syntax => PREVIOUSMONTH(date_column), PREVIOUSQUARTER(date_column), PREVIOUSYEAR(date_column)
Example => Sales Previous Month = CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSMONTH(DimDate[Date]))
Example => Sales Previous Quarter = CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSQUARTER(DimDate[Date]))
Example => Sales Previous Year = CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSYEAR(DimDate[Date]))

Other commonly used time intelligence functions in DAX are

Function Name	Description	Syntax (Example)
DATESYTD	Returns a set of dates for the Year-To-Date. `DATESYTD(date_column, [year_end_date])`	`Sales YTD = CALCULATE(SUM(Sales[SalesAmount]), DATESYTD(DimDate[Date]))`
DATESQTD	Returns a set of dates for the Quarter-To-Date. `DATESQTD(date_column)`	`Sales QTD = CALCULATE(SUM(Sales[SalesAmount]), DATESQTD(DimDate[Date]))`
DATESMTD	Returns a set of dates for the Month-To-Date. `DATESMTD(date_column)`	`Sales MTD = CALCULATE(SUM(Sales[SalesAmount]), DATESMTD(DimDate[Date]))`
DATESYTD	It returns all dates from the year for the current date	`DATESYTD(date_column)`
DATESBETWEEN	It returns a table of dates between two specified dates	`DATESBETWEEN(date_table[column], start_date, end_date)`
DATEADD	It returns a new date, shifted by a specified number of intervals from the first date (days, months, quarters, years)	`DATEADD(start_date, number, interval)`
PARALLELPERIOD	It returns a set of dates, shifted by a specified number of intervals from the given dates in the same level of granularity as that of the given dates.	`PARALLELPERIOD(date_column, number, interval)`
STARTOFYEAR	It returns the first date of the year for the given date.	`STARTOFYEAR(date_column)`
ENDOFYEAR	It returns the last date of the year for the given date.	`ENDOFYEAR(date_column)`

DAX – Logical Functions

In Power BI, DAX (Data Analysis Expressions) provides logical functions that enable us to set conditions, make decisions about our data, and return values appropriate for such conditions. Some common DAX logical functions are the IF, AND, OR, NOT & SWITCH functions. These functions can be used either on their own or together to create complex logical expressions.

`IF` function

IF function perform its role on the basis of conditional evaluations. Its result is based on what condition we have applied.

Syntax – IF(<logical_test>, <value_if_true>[, <value_if_false>])

Example – Create a new column to evaluate Profit greater than 150000 or not. If greater then return HIGH, otherwise return LOW.

Profit Status = IF('Startups'[PROFIT] >= 150000,"HIGH", "LOW")

DAX - IF Function — **DAX – IF Function**

`AND` function

AND function works with multiple conditions and if all the conditions are true, than it return True, otherwise False.

Syntax => AND(<logical1>,<logical2>)

Example – Here we want to check if New York profit is more than 150000 or not. If both conditions are True then return “Valid” otherwise “Invalid”.

Formula => New York Profit = IF(AND('50_Startups'[STATE] = "New York", '50_Startups'[PROFIT] > 150000), "Valid", "Invalid")

DAX - AND Function — **DAX – AND Function**

`OR` function

OR function is used to check multiple conditions and in case at least any one condition is true, then function return True otherwise False.

Syntax => OR(<logical1>,<logical2>)

Example – We will check either the state is California or the Profit is more than 150000. If either of the condition is True, then return High otherwise Low.

Formula => California Profit = IF(OR('50_Startups'[STATE] = "California", '50_Startups'[PROFIT] > 150000), "High", "Low")

DAX - OR Function — **DAX – OR Function**

`NOT` function

NOT function is use to reverse the result of a condition. It converts False to True and True to False.

Syntax => NOT(<logical>)

Example – Reverses the result of a condition where State is California. Not valid if state is California.

Formula => Note = IF(NOT('50_Startups'[STATE] = "California"), "Valid", "Not Valid")

DAX - NOT Function — **DAX – NOT Function**

`SWITCH` function

SWITCH function is used to evaluate an expression against a list of values and returns corresponding results. It is useful when we have to deal with multiple conditions.

Syntax => SWITCH(<expression>, <value1>, <result1>, <value2>, <result2>,..., <default>)

Example – Categorize the Profit into High, Medium and Low category

Formula => Profit Category = SWITCH(TRUE(),'50_Startups'[PROFIT] < 50000, "Low",'50_Startups'[PROFIT] >= 50000 && '50_Startups'[PROFIT]<= 100000, "Medium",'50_Startups'[PROFIT] > 150000, "High")

DAX – Filter Function

In Power BI, Filter of data helps to remove the irrelevant data, keeps only relevant data required for analysis and reduces the size of data as well. DAX contains various filter functions that allow us to play with the data contexts. These functions filter data in calculations and perform dynamic data analysis. Some of the DAX filter functions are:

`FILTER` function

FILTER function is used to return a table that represents a subset of another table or a column based on a specified filter condition.

Syntax => FILTER(<table>, <filter_expression>)

Example – Filter the startups who has profit more than 190000.

Formula => Filtered Profit = FILTER('50_Startups', '50_Startups'[PROFIT] > 190000)

DAX - FILTER function — **DAX – FILTER function**

`CALCULATE` function

CALCULATE function is used to change or modify the context in which data is evaluated. This is used with aggregation functions.

Syntax => CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])

Example – Calculate the sum of profits more than 100000.

Formula => Total Profit = CALCULATE(SUM('50_Startups'[PROFIT]), '50_Startups'[PROFIT] > 100000)

DAX - CALCULATE function — **DAX – CALCULATE function**

`ALL` function

ALL function is used to remove filters from a table or column. It allows to perform calculations over the entire dataset.

Syntax => ALL(<table_or_column>)

Example – Calculate the sum of profit for all the states.

Formula => Total Profit All states = CALCULATE(SUM('50_Startups'[PROFIT]), ALL('50_Startups'[STATE]))

`ALLEXCEPT` function

ALLEXCEPT function is used to removes all filters from a table except for the specified columns.

Syntax => ALLEXCEPT(<table>, <column1>, <column2>, ...)

Example – Calculate the sum of profit except RND department.

Formula => Total Profit By Category = CALCULATE(SUM('50_Startups'[PROFIT]), ALLEXCEPT('50_Startups', '50_Startups'[RND]))

DAX - ALLEXCEPT function — **DAX – ALLEXCEPT function**

`REMOVEFILTERS` function

REMOVEFILTERS function is quite similar to ALL. This is used to removes filters from specified columns or tables.

Syntax – REMOVEFILTERS(<table_or_column>)

Example – Calculate the sum of laptop prices after removing the filter of warranty.

Formula => Total prices WithoutFilters = CALCULATE(SUM('laptopPrice (1)'[Price]), REMOVEFILTERS('laptopPrice (1)'[warranty]))

DAX - REMOVEFILTERS function — DAX – REMOVEFILTERS function

`VALUES` function

VALUES function returns a one-column table that contains the distinct values from the specified column, effectively acting as a filter.

Syntax – VALUES(<column>)

Example – Filter the value of unique names of laptop brands present in the table.

Formula => Unique brands = COUNTROWS(VALUES('laptopPrice (1)'[brand]))

Power BI - Value function — **Power BI – Value function**

These DAX filtering functions are highly effective in analyzing, filtering, and summarizing data in Power BI. Some other functions like USERELATIONSHIP, RELATED, TOPN, DISTINCT etc are also belongs to filtering functions. These filter functions allow us to write advanced measures and calculations in DAX.

Summary

In this article, we learned about DAX functions in Power BI. Following topics were discussed:

Power BI – DAX Functions

DAX Functions