Dax Function: DAY
Category: Date and Time Functions
The DAY function in Power BI is a DAX (Data Analysis Expressions) function used to extract the day component of a date. This function returns an integer representing the day of the month from a date value.
Purpose
To retrieve the day from a given date.
Type of Calculations
It performs simple extraction operations on datetime values.
Practical Use Cases
- Extracting the day component for grouping or filtering data.
- Creating custom calculations or labels based on the day of the month.
- Supporting financial or operational analysis that requires day-level granularity.
DAY(<date>)
| Parameter | Type | Description |
|---|---|---|
date | Datetime | A datetime value or a column containing datetime values. It can also accept text dates that can be converted to datetime. |
How Does DAY Dax Function Works
- The function takes a valid datetime input.
- It identifies the day component of the input date.
- Returns the day as an integer.
Example: For the date 2025-02-10, the function will return 10.
What Does It Return?
- Type: Integer.
- Meaning: Returns a number from 1 to 31, representing the day of the month for the given date.
When Should We Use It?
- Extracting the day part for calculations such as identifying specific events or milestones in a month.
- Grouping or filtering data based on days of the month.
- Supporting conditional logic where decisions depend on the day of the month.
Examples
Basic Usage
Extract the day from a single date:
DAY(DATE(2025, 2, 10))
Output: 10.
Column Usage:
Apply the function to a column of datetime values:
DayColumn = DAY(Table[OrderDate])
For a row with OrderDate = 2025-03-15, the function will return 15.
Advanced Usage
Combine the DAY function with conditional logic:
IsWeekend = IF(DAY(Table[Date]) IN {1, 15}, "Special Day", "Regular Day")
This categorizes dates where the day is either the 1st or 15th as “Special Day.”
Tips and Tricks
- Ensure the
dateparameter is in a valid datetime format. Use functions likeDATEVALUEorDATEto clean the input if necessary. - For consistent formatting, combine
DAYwithMONTHandYEARto extract all parts of a date efficiently. - Use in conjunction with
WEEKDAYorWEEKNUMfor detailed calendar-based logic.
Potential Pitfalls
- Input errors: Non-datetime inputs or invalid date strings will result in errors.
- Locale considerations: Ensure the date format aligns with the system’s locale settings for text-based dates.
Performance Impact of DAY DAX Function:
- The function performs optimally on columns already formatted as datetime.
- For large datasets, preprocess text-based dates in Power Query to minimize runtime errors.
Related Functions You Might Need
- MONTH: Extracts the month component from a date.
- YEAR: Extracts the year component from a date.
- WEEKDAY: Identifies the day of the week from a date.
- DATE: Constructs a date from year, month, and day components.
Want to Learn More?
For more information, check out the official Microsoft documentation for DAY. You can also experiment with this function in your Power BI reports to explore its capabilities.
Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.
The DAY function extracts the day of the month from a given date and returns it as an integer.
Yes, if the text date can be converted to a valid datetime format. Use DATEVALUE if needed.
The function returns a number between 1 and 31, corresponding to the day of the month.
The function generates an error if the input cannot be interpreted as a valid date.
Yes, the function works seamlessly in calculated columns and measures for extracting the day from datetime data.
Established in 2020, Lets Viz Technologies provides a full range of high-quality data analysis and data visualization services. We are also an authorized Zoho Partner.
Sitelinks
