Day 12: Text, Date & Time Functions and Creating Tables in Power BI DAX
On Day 12, we explore DAX text functions, date & time functions, and how to create new tables for reporting and analysis. These functions are essential for transforming and enriching your dataset.
1. Text Functions
DAX provides several functions to manipulate text data efficiently:
-
UPPER() – Converts a string to uppercase.
-
LOWER() – Converts a string to lowercase.
-
LEFT() – Extracts a specified number of characters from the left side of a string.
-
RIGHT() – Extracts characters from the right side of a string.
-
MID() – Extracts characters from the middle of a string based on a starting position and length.
-
CONCATENATE() – Combines two columns only. Cannot include spaces or special characters.
-
Ampersand (&) – Combines more than two columns and allows adding spaces or special characters.
-
SUBSTITUTE() – Replaces a specific word or substring with another word.
-
REPLACE() – Replaces characters at specific positions with new characters.
-
SEARCH() – Finds the position of a substring within a string.
-
TRIM() – Removes extra spaces from text.
Example:
This combines first and last names with a space in between.
2. Date and Time Functions
2.1 Date Functions
-
YEAR() – Extracts the year from a date.
-
QUARTER() – Extracts the quarter (1–4) from a date.
-
MONTH() – Extracts the month number.
-
WEEKDAY() – Returns the day of the week as a number.
-
WEEKNUM() – Returns the week number of the year.
-
DATE() – Creates a date from year, month, and day.
-
DATEDIFF() – Calculates the difference between two dates in days, months, or years.
2.2 Time Functions
-
HOUR() – Extracts the hour component from a time.
-
MINUTE() – Extracts the minute component.
-
SECOND() – Extracts the second component.
2.3 Current Date and Time
-
NOW() – Returns the current date and time.
-
TODAY() – Returns the current date.
-
UTCNOW() – Returns the current date and time in UTC.
-
UTCTODAY() – Returns the current date in UTC.
3. Creating New Tables
DAX allows generating tables for analysis or time intelligence:
-
CALENDAR() – Generate dates manually between a start date and end date.
-
CALENDARAUTO() – Automatically generates dates based on the existing date columns in your data model.
Example:
This creates a complete date table automatically, which is useful for time-based calculations like YTD, QTD, and MTD.
Using text, date, and time functions along with new tables allows you to prepare datasets efficiently, enabling dynamic calculations, reporting, and deeper insights in Power BI.
