Daily DAX : Day 248 MONTH

 The MONTH function in Power BI DAX (Data Analysis Expressions) extracts the month number from a given date, returning an integer between 1 (January) and 12 (December). It’s commonly used for date-related calculations, filtering, or grouping data by month in reports.

Syntax

dax

MONTH(date)

    date: A date expression or a column containing date values. The input must be a valid date in datetime format.

Return Value

An integer from 1 to 12 representing the month of the specified date.

Use Case

The MONTH function is useful in scenarios where you need to:

    Extract the month from a date for reporting or analysis (e.g., sales by month).

    Group or filter data based on months.

    Create calculated columns or measures for time-based calculations.

Example 1: Creating a Calculated Column

Suppose you have a table Sales with a column OrderDate. You want to create a new column to display the month number of each order.

dax

MonthNumber = MONTH(Sales[OrderDate])

If OrderDate is 2025-06-18, the MonthNumber column will return 6 (June).

Example 2: Aggregating Sales by Month

To calculate total sales for each month, you can use the MONTH function in combination with other DAX functions like CALCULATE and SUM. For example:

dax

TotalSalesByMonth = 

CALCULATE(

    SUM(Sales[Amount]),

    ALLEXCEPT(Sales, Sales[OrderDate]),

    MONTH(Sales[OrderDate]) = 6

)

This measure calculates the total sales amount for June (month 6) across all years.

Example 3: Displaying Month Names

To display the month name instead of the number, you can combine MONTH with FORMAT:

dax

MonthName = FORMAT(MONTH(Sales[OrderDate]), "mmmm")

For 2025-06-18, this returns June.

Key Notes

    If the input date is blank or invalid, the MONTH function returns an error unless handled with error-handling functions like IFERROR.

    For sorting months chronologically in visuals, ensure the month column is paired with a year or use a custom sort column, as MONTH alone doesn’t account for the year.

    The function works with any valid date column, such as Date[OrderDate] or a calculated date like TODAY().

Practical Scenario

Imagine a retail company analyzing monthly sales trends. You can use the MONTH function to group sales data by month, create visuals like bar charts showing sales per month, or filter a report to focus on specific months (e.g., holiday season months like November and December).

For more complex date manipulations, combine MONTH with other DAX functions like YEAR, DAY, or DATEADD to build robust time intelligence calculations.