Time Intelligence & Date Table Use

Hi sfalk781 

You've raised a good point. There could be multiple ways of addressing this.

The time intelligence functions all take the initial date filter context and modify it in some way.

In particular, the DATESMTD, DATESQTD, DATESYTD functions look at the latest date in the current filter context and produce a modifed date filter for the MTD/QTD/YTD period "as at" that latest date.

So, if your date table extends into the future to 2033 as in your example, and no filters are applied to DimDate, the latest date is in 2033 and you likely have no data during the YTD period ending some time in 2033.

A possible solution if you want your dates to be automatically limited to no later than the latest date in your fact table would be to write measures using this pattern:

MTD Measure =
VAR GlobalMaxDate = 
    CALCULATE ( MAX ( FactTable[Date] ), REMOVEFILTERS () )
RETURN
CALCULATE ( 
    TOTALMTD ( [Total XYZ Reported], DimDate[Calendar Date] ), -- original measure
    KEEPFILTERS ( DimDate[Calendar Date] <= GlobalMaxDate ) -- enforce upper-bound on Date
)

The definition of GlobalMaxDate could equally be something else that makes sense, such as TODAY().

Writing this for every measure might be tedious, so Calculation Groups would probably be a good idea to handle all time intelligence logic.

Those are a few ideas anyway.

Regards,

Owen