DAX Date Functions In Power BI

DAX Date Functions In Power BI

DAX offers a broad set of date functions in Power BI that help you analyze your data over time and gain valuable insights. By using DAX date functions In Power BI, you can better understand how your data changes over time. They help you build reports that automatically adjust based on user interactions and filters.

Learn Power BI Date Function with Be10x’s Power BI Online Course at just Rs.9. 

5

What is DAX?

DAX, or Data Analysis Expressions, is a formula language used in Microsoft Power BI, Analysis Services, and Power Pivot in Excel. It allows you to create calculations, filter data, and perform advanced analysis on your data models. Here are some features of DAX: 

  • DAX is specifically designed for working with data models, not general-purpose programming.
  • DAX offers many functions for working with dates, strings, numbers, logical values, and relationships between tables. 
  • You can use DAX to create calculated columns that add new data points to your tables and measures that provide aggregate summaries of your data. 
  • DAX formulas can automatically adjust based on user interactions and filters in your reports and visualizations.

Importance of DAX Date Functions in Power BI

DAX Date functions are essential as they allow you to analyze and understand your data over time. DAX Date functions are necessary for tasks such as:

  • Analyzing how your data changes over months, quarters, or years.
  • Comparing results for different periods (e.g., comparing sales this month to last month).
  • Selecting specific date ranges for analysis.
  • Calculating metrics like year-to-date sales, moving averages, or percentage changes over time.

Common DAX Date Functions in Power BI

Some of the common DAX Date Functions are:

  • TODAY(): Returns the current date.
  • YEAR(): Extracts the year from a date.
  • MONTH(): Extracts the month from a date.
  • DAY(): Extracts the day of the month.
  • DATESYTD(): Filters a date table to include dates from the beginning of the current year.
  • DATESMTD(): Filters a date table to include dates from the beginning of the current month.
  • SAMEPERIODLASTYEAR(): Returns dates corresponding to the same period in the previous year.
  • EOMONTH(): Returns the last day of a month, optionally after or before a specified number of months.
  • DATEDIFF(): Calculates the difference between two dates in selected units (days, months, years).
DAX Date Functions In Power BI

Let us study some of these DAX date functions in Power BI with examples. 

DAX Date Function TODAY()

The TODAY() DAX date function is the real-time clock for your data. When you use this function, it fetches the current date from your computer’s system clock.

Syntax: TODAY() 

It has no arguments and directly returns a value based on the current date.

DAX Date Functions In Power BI

The TODAY() DAX date function offers various applications in your Power BI reports:

  • Use it in text boxes or calculated columns to show the report’s last refresh date, creating an “as of” indicator for viewers.
  • Combine TODAY() with logical operators like >, <, or BETWEEN to create active filters based on the current date. For example, filter data for the current month: Date >= TODAY().
  • Use TODAY() as a reference point for calculating durations, time differences, or date-based comparisons.

DAX Date Function NOW()

The NOW() function in DAX returns the current date and time, including the date and time of day, when your report is refreshed. 

Syntax: NOW() 

It takes no arguments and directly returns the current date and time.

DAX Date Functions In Power BI

The NOW() DAX date function offers various applications in your Power BI reports:

  • Include NOW() in text boxes or calculated columns to show the exact date and time the report was last refreshed, providing valuable context for viewers.
  • Use NOW() as a reference point for calculating durations, time differences, or other time-sensitive metrics. For example, calculate the time remaining until a deadline: DATEDIFF(MINUTE, NOW(), ‘Deadline Time’).
  • Combine NOW() with logical operators and time filters to create dynamic filters based on the current date and time. For instance, filter data for the current hour: Hour(Now()) = HOUR(‘YourTable'[TimeColumn]).

DAX Date Function YEAR()

The YEAR() DAX date function is essential for extracting the year component from a date value. 

Syntax: YEAR([DateColumnName])

Input: Accepts a single argument, which can be a:

  • Date column reference from your table
  • Date enclosed in quotes (e.g., “2024-02-13”)

Output: Returns an integer representing the year (e.g., 2024 for today’s date)

DAX Date Functions In Power BI

The YEAR() DAX date function offers various applications in your Power BI reports:

  • Using YEAR(), you can create a new column that extracts the year for each sale. 
  • If your dataset includes birthdates, YEAR() helps calculate the age of individuals. 
  • Many businesses operate on fiscal years that don’t align with the calendar year. YEAR() is crucial for creating custom budgetary year reports. 

DAX Date Function MONTH()

The MONTH() DAX date function is another crucial tool for analyzing your data based on specific months.

Syntax: MONTH([DateColumnName])

Input: Similar to YEAR(), it accepts a date column reference, date literal, or expression that evaluates to a date value.

Output: Returns an integer between 1 and 12, representing the month number (e.g., 2 for February).

DAX Date Functions In Power BI

The MONTH() DAX date function offers various applications in your Power BI reports:

  • By applying MONTH(), you can create a new column that extracts the month for each sale. This makes it easy to analyze and compare sales performance every month.
  • If your data exhibits seasonal patterns, MONTH() helps you find them. Grouping data by month lets you identify trends, such as increased sales during certain months or seasonal variations.
  • MONTH() helps create custom reporting periods. You can use it to filter data for a specific month, making generating reports tailored to your organization’s needs easy.

DAX Date Function TOTALYTD()

The TOTALYTD() DAX date function is valuable for analyzing your data’s year-to-date (YTD) performance in Power BI. 

Syntax: TOTALYTD([Expression], <Dates>[, <Filter>][, <YearEndDate>])

Input:

Expression: Any scalar value you want to aggregate (e.g., SUM(Sales[Amount])).

  • Dates (optional): A date column reference, table expression returning dates, or Boolean expression defining a table of dates. Defaults to the current date’s context.
  • Filter (optional): An additional filter expression to apply to the dates.
  • YearEndDate (optional): A literal string specifying the end date of your fiscal year (e.g., “06/30” for June 30th). It defaults to December 31st.

Output: Returns the calculated YTD value based on the specified expression and date criteria.

The TOTALYTD() DAX date function offers various applications in your Power BI reports:

  • TOTALYTD() is invaluable for tracking sales or revenue figures throughout the year. It gives you a running total and helps you understand your performance relative to your targets or previous years.
  • TOTALYTD() allows you to see the annual cumulative expenditure for expense-related metrics. 
  • Use TOTALYTD() to analyze the year-to-date profit. It aids in understanding the ongoing profitability of your business
  • TOTALYTD() is excellent for creating dynamic visuals. For instance, you can use it to generate a line chart showing a metric’s accumulation over time, providing a clear picture of trends.

DAX Date Function SAMEPERIODLASTYEAR()

5

The SAMEPERIODLASTYEAR() function in Power BI is a powerful tool for analyzing trends and performance by comparing corresponding periods in the previous year. 

Syntax: SAMEPERIODLASTYEAR([DatesColumn])

Input: Takes a single argument, which refers to a date column in your table.

Output: Returns a table with one column containing dates shifted one year from the corresponding dates in the input column. This allows for easy side-by-side comparisons with the previous year.

The SAMEPERIODLASTYEAR() DAX date function offers various applications in your Power BI reports:

  • Compare monthly sales figures for the current year to the same months in the previous year to identify sales growth or decline and adjust strategies accordingly.
  • Analyze website traffic and conversion rates year-over-year for specific marketing campaigns to understand their effectiveness and optimize future campaigns.
  • Track customer churn rate month-over-month and compare it to the previous year to identify areas for improvement in customer retention.
  • Monitor investment returns quarter-over-quarter, comparing them to the previous year to assess risk and performance.

Power BI DAX Date Format

Formatting dates in Power BI DAX is essential for presenting your data clearly and understandably. 

Here are the DAX date format functions: 

  • FORMAT(): Provides flexibility for custom formatting using specific codes.
  • DATEVALUE(): Converts a formatted date string to a date value for calculations.

Be10x Power BI Workshop: Learn DAX Function in Power BI

Want to master data analytics and learn DAX functions in Power BI? It’s time for you to join the Be10x Power BI workshop to become a top-tier PowerBI user. Led by Aditya Goenka, a Data analytics expert and alumni from IIT Kharagpur, this Power BI workshop is here to make data exciting and easy for you.

What makes this Power BI workshop unique? It’s for everyone, even if you don’t know much about data analytics and Power BI. Be10x  workshop can increase your salary to 3 times more and keep your job safe. And guess what? It is just Rs. 9; if you join before the deadline, you get bonuses worth Rs. 11,500. 

Learn more about Power BI Workshop by Be10x and register now.