MTD measure for the MAX year and month selected in slicer

[Zosy]

Hi,

How can I dinamically calculate the month to date value for the last month selected on a slicer? At the moment I have it set for the current year only, but I would like to be able to calculate it based on the slicer selection, where there are different years.

MONTH TO DATE = CALCULATE(TOTALMTD(SUM('Orders'[Amount]),'Date'[Date]),FILTER('Date', 'Date'[Year]= YEAR(TODAY())))

I have a date table

Date = 

VAR StartDate = DATE ( YEAR ( MIN ('Orders'[Date]) ), 1,1)
VAR EndDate = DATE ( YEAR ( MAX ('Orders'[Date]) ), MONTH( MAX ('Orders'[Date])), DAY( MAX ('Orders'[Date])))

return
ADDCOLUMNS(CALENDAR(
        StartDate,
        EndDate), 

        "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), 
        "Year", YEAR ( [Date] ), 
        "MonthNo", FORMAT ( [Date], "MM" ), 
        "YearMonthNo", FORMAT ( [Date], "YYYY/MM" ), 
        "YearMonth", FORMAT ( [Date], "YYYY/mmm" ), 
        "MonthShort", FORMAT ( [Date], "mmm" ), 
        "MonthLong", FORMAT ( [Date], "mmmm" ), 
        "WeekNo", WEEKDAY ( [Date] ), 
        "WeekDay", FORMAT ( [Date], "dddd" ), 
        "WeekDayShort", FORMAT ( [Date], "dddd" ), 
        "Quarter", "Q" & FORMAT ( [Date], "Q" ), 
        "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date],"Q" )
)

 Thank you

[danextian]

Hi @Zosy 

Wether you use DATESMTD or TOTALMTD, both return the MTD value of the max date in your calendar table. So if in the current context there is no specific month such as in the case of the grand total row, the MTD value will be that of the latest month-yr. Please see screenshot below and the attached sample pbix.

[DataNinja777 · Accepted solution]

Hi @Zosy ,

You can adjust your MONTH TO DATE measure to calculate the value dynamically based on the last month selected in the slicer, regardless of the year. The key is to capture the most recent date from the slicer context and filter your calculation accordingly.

Here’s the revised measure:

MONTH TO DATE =
VAR LastSelectedDate = MAX('Date'[Date])
VAR LastSelectedMonth = MONTH(LastSelectedDate)
VAR LastSelectedYear = YEAR(LastSelectedDate)
RETURN
CALCULATE(
    TOTALMTD(
        SUM('Orders'[Amount]),
        'Date'[Date]
    ),
    'Date'[Year] = LastSelectedYear,
    'Date'[MonthNo] = LastSelectedMonth
)

This version calculates the month-to-date total for the latest selected month and year. The measure works by first identifying the most recent date selected in the slicer using MAX('Date'[Date]), then extracting the month and year from that date. The CALCULATE function applies the TOTALMTD calculation filtered to match the extracted year and month.

If your slicer setup is more complex and you want to ensure it handles all slicer filters properly, you can use ALLSELECTED instead of MAX to get the last selected date in a broader context:

MONTH TO DATE =
VAR LastSelectedDate = MAXX(ALLSELECTED('Date'), 'Date'[Date])
VAR LastSelectedMonth = MONTH(LastSelectedDate)
VAR LastSelectedYear = YEAR(LastSelectedDate)
RETURN
CALCULATE(
    TOTALMTD(
        SUM('Orders'[Amount]),
        'Date'[Date]
    ),
    'Date'[Year] = LastSelectedYear,
    'Date'[MonthNo] = LastSelectedMonth
)

This approach ensures that your measure dynamically adapts to the slicer selection and calculates the month-to-date value for the most recent month available in the slicer, regardless of the year.

Best regards,

[Zosy]

Thank you for explaining it. It worked!