TOTALMTD DAX Function in Power BI

The TOTALMTD DAX function evaluates the value of the expression for the month to date, in the current context. "MTD" stands for "Month-to-Date," so TOTALMTD calculates a total for the current month up to the current date.

DAX Syntax TOTALMTD(expression, dates[,filter])

The function has the following parameters:

expression: It specifies an expression that returns a scalar value.
dates: It specifies the column that contains dates.
filter:Optional. An expression that specifies a filter to apply to the current context.

Example: Let’s create a measure with name "TOTALMTD Measure".

DAX

TOTALMTD Measure = TOTALMTD(SUM(Sales[Profit]), Sales[Date])

The output of the above dax code is shown below:

Every start of the month the expression is reevaluated as shown below:

Note: In the bottom the value 36934 is the latest total Month to date value.

When we add any categorical column then the measure is filtered with this context.

Here, on 5th January 2023 the value in the TotalYTD Measure is 1021 which is the sum of the 252 and 769 value as both are of the same categorical value as Category column value applies the filter on the TotalYTD Measure.

To remove the categorical filter context on the measure we can modify the dax formula:

DAX

TOTALMTD Measure = 
    TOTALMTD(
        SUM(Sales[Profit]), 
        Sales[Date], 
        ALL(Sales[Category])
    )

The output of the above dax code is shown below:

Previous Next