Time Intelligence & Date Table Use

[sfalk781]

I did some searching on this and I see what's causing it, but not sure how to fix it.  I have a dataset and a date dimension table, both connected via a date field.  My date table goes years in the future and there are no spaces but what I'm finding is that when I create a time intelligence calculation, it's scanning to the end of the date table, in this case 2033 and returns a zero.  If I use a slicer and filter it down to cover the period of interest, the number is correct.  It doesn't matter if we're talking about YTD, QTD, MTD, etc.

For example, let's say I want to to a TotalMTD and I write:

TOTALMTD([Total XYZ Reported],DimDate[Calendar Date])

The only way that this doesn't return a zero is if I either have a date slicer that restricts the data to no further than this month, or limit the data to only up to this month in powerquery.  The same is required for Quarter, Year, etc.

How do you work around this?

[sfalk781]

Thanks alot @OwenAuger for helping me with this.  As a relatively new user, these problems can take forever to figure out.  I'll research the calculation groups, but I was able to find two other methods in addition to what you suggested. 

• Limit the date table from generating anything past the current day by adding <= getdate() in the query
• Add an identifer in the date table that searches the fact table for the MAX date, then it codes each row in the date table as true or false.  True means there is a record, false means there isn't and it shouldn't be considered.  From there, you would add this True indicator as a filter to the DAX code.

For the second bullet above, the code would look like this 

Total MTD = Calculate([Total XYZ Reported],CALCULATETABLE(DATESMTD(DimDate[Calendar Date]),DimDate[Dates W/ XYZ]=True))

I found this suggestion here - https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/ 

[OwenAuger · Accepted solution]

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