Forum Discussion
Time Intelligence Functions not working - what am I doing wrong?
I have raised several questions over Time Intelligence functions which made me realise I'm probably doing something fundamentally wrong with then.
At the moment I have a SalesTable that is a DirectQuery connection and two custom-made data tables, DateTable1 & DateTable2.
DateTable1 is not explicitly marked as a date table in Model view while DateTable2 is. This was to test if that will make a difference.
Both have their respective [date] columns marked as Date only, not DateTime.
Both are conneted to the date column in the Sales table through one-way one-to-many relationships and both will correctly filter data from SalesTable if used in filters or even if I build custom time intelligence functions. (DateTable1[date] >= ....... && .....)
In other words, it's not a relationship problem that is stopping time intelligence working or the structure of the data in the tables. Manual filtering works fine.
I am trying to use the TOTALMTD or DATEMTD funtions to calculate the SUM of Sales for this month, and then another one for last month and nothing I have tried seems to be working.
I've tried the following:
MTD_Usage =
CALCULATE(
SUM(Sales[sales]),
DATESMTD(DateTable 1 or 2 [date])
)
MTD_Usage =
TOTALMTD(
SUM(Sales[sales]),
DateTable 1 or 2 [date]
)
MTD_Usage =
CALCULATE(
SUM(Sales[sales]),
DATESMTD(DateTable 1 or 2 [date]),
ALL (Sales)
)
I have also created a separate measure within the Sales table called TotalSales, to SUM the whole [sales] and then used that instead of the SUM(Sales[sales]) expression above. Again this TotalSales works perfectly fine outside of Time Intelligence functions but not with them.
It's clear that I'm doing something wrong so my question is, what do I need to change?
I have check with direct query and I get the same result. I wasn't aware of this restriction myself as I usually load my tables. Here is the documentation https://learn.microsoft.com/en-us/dax/?view=sql-server-2017
7 Replies
I get blank results when using the above approaches. Just thinking it's because of DirectQuery, would that make sense?
I have check with direct query and I get the same result. I wasn't aware of this restriction myself as I usually load my tables. Here is the documentation https://learn.microsoft.com/en-us/dax/?view=sql-server-2017
SevsBo
Your issue likely stems from DirectQuery limitations, an unmarked date table, or an incorrect relationship structure. The best fix is to switch your DateTable to Import Mode and mark it as a Date Table in Model View. If DirectQuery is required, a workaround is to use CALCULATE with FILTER + ALL instead of TOTALMTD/DATESMTD. Alternatively, you can pre-aggregate MTD values at the database level to improve performance.Hiiii SevsBo
You have to mark the datetable as "Mark as Date Table"
this will easily resolve your issue.If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!Hi SevsBo , Thank you for reaching out to the Microsoft Community Forum.
Please let us know if your issue is solved. If it is, consider marking the answers that helped 'Accept as Solution', so others with similar queries can find them easily. If not, please share the details.
Thank you.Hi SevsBo , Thanks for the update. We are happy to hear that you have resolved the issue. Thanks for sharing the details here. Please mark your insights 'Accept as solution' to help others with similar problems find it easily.
Thank you.
