Forum Discussion
Using MTD and QTD
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/
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
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/