Dax Function: MONTH
Category: Date and Time Functions
The MONTH function in Power BI is a DAX (Data Analysis Expressions) function used to extract the month component from a given date or datetime value.
Purpose:
To retrieve the month number (1–12) from a date.
Type of Calculations:
Date-based segmentation and analysis.
Practical Use Cases:
- Filtering or grouping data by month.
- Performing time-series analysis based on monthly trends.
- Calculating KPIs for specific months.
MONTH(<date>)
| Parameter | Type | Description |
|---|---|---|
date | Datetime | A valid date or datetime value from which the month component will be extracted. |
How Does MONTH Dax Function Works
- The function takes a valid date or datetime input.
- It extracts the month component from the input and returns it as a number.
- Example: For
2025-07-15, the function returns7.
- Example: For
What Does It Return?
- Type: Integer.
- Meaning: Returns a numeric value between
1(January) and12(December), representing the month of the given date.
When Should We Use It?
- When segmenting data by months for reporting purposes.
- To filter or group data dynamically based on specific months.
- To create monthly aggregates, trends, or time-based calculations.
Examples
Basic Usage
Extract the current month from the system date:
MONTH(TODAY())
Output: If today’s date is 2025-02-10, the result is 2.
Column Usage:
Add a calculated column to extract the month from a date column:
OrderMonth = MONTH(Table[OrderDate])
For OrderDate = 2023-03-21, the result is 3.
Advanced Usage
Combine with other functions to calculate sales for the current month:
SalesThisMonth = CALCULATE(
SUM(Sales[Amount]),
MONTH(Sales[Date]) = MONTH(TODAY())
)
This formula sums the sales amounts for the current month.
Tips and Tricks
- Use MONTH with functions like YEAR and DAY to decompose a date into its components.
- For text-based months, combine with the FORMAT function:
FORMAT([OrderDate], "MMMM")
This returns the full month name (e.g., “January”). - Ensure date fields are properly formatted as date or datetime types to avoid errors.
Potential Pitfalls
- Null or invalid date inputs will result in an error. Use
ISBLANKto handle missing values. - If working with fiscal months, the standard MONTH function might not align with fiscal periods.
Performance Impact of MONTH DAX Function:
- The MONTH function performs well with scalar or columnar data.
- For large datasets, precomputing the month column in Power Query can improve performance.
Related Functions You Might Need
- YEAR: Extracts the year component from a date.
- DAY: Extracts the day component from a date.
- FORMAT: Converts a date into a formatted string (e.g., month names).
- TODAY: Returns the current date.
- NOW: Returns the current date and time.
Want to Learn More?
For more information, check out the official Microsoft documentation for MONTH. 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 MONTH function extracts the month number (1–12) from a given date or datetime value.
No, the input must include a valid date component to extract the month.
It will return an error. Use IFERROR or ISBLANK to manage null inputs.
No, text-based dates must first be converted to date or datetime format using DATEVALUE or similar functions.
While MONTH returns the numeric month (1–12), FORMAT can return month names as text (e.g., “January”).
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
