SAMEPERIODLASTYEAR makes my report too slow in DirectQuery

[hortiz4]

Hi everyone,

I have a report with 4 visuals. One of them works without SAMEPERIODLASTYEAR and it's DAX query takes 75ms. But the other 3 takes at least 300ms. Debugging the slowest query I see this huge union:

((SELECT CAST( '20190301 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190302 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190303 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190304 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190305 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190306 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190307 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190308 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190309 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190310 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190311 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190312 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190313 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190314 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190315 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190316 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190317 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190318 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190319 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190320 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190321 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190322 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190323 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190324 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190325 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190326 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190327 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190328 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190329 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190330 00:00:00' AS datetime) AS [c94] )  UNION ALL 
(SELECT CAST( '20190331 00:00:00' AS datetime) AS [c94] ) )

Can be this the reason for those times?

Is there a way to avoid all this SELECT CAST?

[Anonymous]

Since you're using Direct Query DAX is being translated into SQL and this native query is sent to your data source. Your data source (say, SQL Server) must have the right constructs in place (e.g., indexes) for the queries to be fast. Slow response times are not a DAX problem. It's your data source problem. You can also try to write a different DAX formula to calculate what you want and see if the underlying SQL changes...

[hortiz4]

I don't know what can I do to solve this problem.

My database has a date table, with datetime format. 

Do you know other ways to calculate SAMEPERIODLASTYEAR? I tried DATEADD(date, -1, YEAR) and returns the same SQL query.

[Anonymous]

@hortiz4

I can't do anything without the .pbix file... Sorry. Troubleshooting slow SQL is not something one can do without the problem in front of one's eyes.

[AllisonKennedy]

Thanks @Anonymous , I completely ignored the title that this was DirectQuery. Ignore my response, you can't use M with DirectQuery.

[AllisonKennedy]

This looks like SQL statement, not DAX. What is it being used for? You can just create a simple date list or table in M using Power Query instead.

let
startDate = #date(2019, 1, 1),
endDate = Date.From(DateTime.LocalNow()),
Dates = List.Dates(startDate, Duration.Days(endDate - startDate), #duration (1,0,0,0)),

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html