SAMEPERIODLASTYEAR (DateADD) not supported in direct query mode

[Anonymous]

Many useful timeintelligence functions like sameperiodlastyear etc are not supported in direct query mode.

How does everyone get around the issue?

I can enable the unrestricted measure option to use them, not sure the performance impacts for that. Anyone can share their experience?

[v-qiuyu-msft]

Hi @Anonymous,

As we know, to use DAX expression in Power BI desktop in DirectQuery mode, we need to allow "unrestricted measures" , otherwise, we are not able to use those DAX expressions. The option you found which is suggested by Microsoft, see: Power BI Updates This Week: New Report Authoring Capabilities.

In addition, depends on different data source types you used, you can also retrieve data same as the DAX expression results on query side. If you are connecting to SQL Server data source, you can use DATEADD() function like below to return results same as sameperiodlastyear() DAX expression results:

SELECT DATEADD(year, -1, '2006-08-30') as previousyear from table1

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[Anonymous]

@v-qiuyu-msft dateadd is also not supported in direct query mode by default.

[v-qiuyu-msft]

Hi @Anonymous,

What I mean is using DATEADD() function in T-sql query when you connect to SQL Server database. See:

Best Regards,
Qiuyun Yu