Using MTD and QTD

[mp390988]

Hello,

I am trying to compute the MTD and QTD revenue across different teams.

I write my MTD measure as:

mMTDRev = CALCULATE(

    [Revenue],

    DATESMTD('CalendarTbl'[Date])

)

And QTD measure as:

mQTDRev = CALCULATE(

    [Revenue],

    DATESQTD('CalendarTbl'[Date])

)

My calendar table is marked as a date table with a date field.

I have a normal matrix visual where I have taken the different team names from the dealergroupfixed table which is a dimension table. Before I drag the two measures I have created I see this:

But as soon as I place one of the measures I get this. 

Why is this happening?

Thank You

[mp390988]

Hi @OwenAuger ,

Brilliant! After reading your post it is now clear to me why my intial measure was not working.

Thank You,
M

[Ashish_Mathur]

Hi,

That measure returns a blank.  I cannot spot a mistake in your measure though.  Looks like some data type issue.  Please share the download link of the PBI file.

[OwenAuger]

Hi @mp390988

Short answer: DATESMTD and DATESQTD functions are evaluated relative to maximum visible date in the filter context, which appears to be the unfiltered (global) maximum date in your example.

More detailed explanation:

First off, the rows disappear because the measure placed in the matrix returns BLANK for all values of DealerGroupFixed. When a Matrix visual contains one or more measures, any row or column of the Matrix in which all measure values are BLANK is excluded from the visual (by default, but can be overridden using Show items with no data).

(Note: When no measures are added to a visual, the visual generates a DAX query with a "default" row-count measure.)

Why are both measures returning blank in your example?

Taking DATESMTD as an example:

• DATESMTD ( 'CalendarTbl'[Date] )returns a table containing a column of dates corresponding to the month-to-date period in the current context (docs).
• Specifically, it returns the dates in the month-to-date period as at the latest visible date in the column 'CalendarTbl'[Date] within the current filter context.
• If no filters have been applied affecting 'CalendarTbl', the latest visible date will be the global maximum value of 'CalendarTbl'[Date], and the month-to-date period ending on that date might very well be a date range for which [Revenue] returns BLANK. This could be the case if no data exists for the dates in the MTD period as at the global maximum date.

DATESQTD behaves similarly, returning a table containing the dates in the quarter-to-date period as at the maximum visible date. All time intelligence functions are referenced to the current context in a similar way. Whether the max/min or entire date range is relevant depends on the function.

I'm guessing you might want the MTD or QTD period to be determined relative to the current date as returned by the TODAY ( ) function. If so, you could adjust your measures to apply a filter on 'CalendarTbl'[Date]  equal to the value returned by TODAY ( ). Assuming 'CalendarTbl' is marked as a date table 'CalendarTbl'[Date] being the date field, here are some possible variations on how this could can be written:

mMTDRev as at TODAY =
CALCULATE(
    [Revenue],
    DATESMTD ( 'CalendarTbl'[Date] = TODAY ( ) ) -- Boolean expression defining single-column date table
)

mMTDRev as at TODAY =
CALCULATE(
    [Revenue],
    CALCULATETABLE (
        DATESMTD ( 'CalendarTbl'[Date] ),
        'CalendarTbl'[Date] = TODAY ( )
    )
)

mMTDRev as at TODAY =
VAR ReferenceDate = TODAY ( )
RETURN
    CALCULATE(
        [Revenue],
        CALCULATETABLE (
            DATESMTD ( 'CalendarTbl'[Date] ),
            'CalendarTbl'[Date] = ReferenceDate
        )
    )

It's possible I've made an incorrect assumption about your model/report setup. If you could provide a link to a sample PBIX that would help diagnose further.

[mp390988]

Hi @OwenAuger ,

Thank you very much for your dedicated time in posting a beautiful answer.
So far it kind of makes sense and anyone of your possible answers, no matter which one gives the correct result.

The reason why I said it "kind" of makes sense is because I am not too sure why when I did this it didn't work which prompted me to post on here. I still don't quite understand why the below does not work?

mMTDRev = CALCULATE(
    [Revenue],
    DATESMTD('CalendarTbl'[Date]),
    CalendarTbl[Date] <= TODAY()
)

Thank You,
M

[OwenAuger · Accepted solution]

Hi @mp390988 

It sounds like the suggested measures are working, which is good news 🙂

To answer your question on the measure you just posted that isn't working:

When multiple filter arguments are provided to CALCULATE, they are evaluated independently (in the existing context) then applied simultaneously when evaluating the expression in the first argument.

mMTDRev =
CALCULATE(
    [Revenue],
    DATESMTD ( 'CalendarTbl'[Date] ), -- Filter 1: MTD in current context
    CalendarTbl[Date] <= TODAY()      -- Filter 2: Dates <= Current date
)

For example, if CalendarTbl is unfiltered, and assuming the maximum values of CalendarTbl[Date] is 31 December 2026 and the current date is 6 May 2026, the two filters would be:

• MTD as at 2026-12-31 = All of December 2026
• All dates up to and including 6 May 2026

These two filters have no intersection, so the [Revenue] measure is blank when both are applied simultaneously.

The difference with the measures I suggested is that they first apply the "today" filter (as an outer filter), then apply the MTD filter (as an inner filter).

Recommended article:

https://www.sqlbi.com/articles/order-of-evaluation-in-calculate-parameters/