SAMEPERIODLASTYEAR with filter return error message

[Onaiggac]

I have two main calculated columns.

The first one is for calculate using the SAMEPERIODLASTYEAR function.

AnoPre = CALCULATE(SUM(vw_VariacaoTotalResiduosXCategoria[Peso(t)]); SAMEPERIODLASTYEAR(DatasParaBI[Date]))

And the variation expression

Δ = IFERROR((SUM(vw_VariacaoTotalResiduosXCategoria[Peso(t)]) - [AnoPre])/SUM(vw_VariacaoTotalResiduosXCategoria[Peso(t)]);BLANK())

The table DatasParaBI is my date table and the vw_VariacaoTotalResiduosXCategoria is my SQL view that return my data.

It works well when my filter was set year biggest than or equal to 2017. But its return an error when I set year equals to 2017.

The error message is something like:

A convertion of nvarchar data type to datetime results in a out of range value. An exception was generated by IDataReader interface.

I wanna show only the actual year.

I already try this solution https://community.powerbi.com/t5/Desktop/SAMEPERIODLASTYEAR-with-a-year-filter/td-p/91501 but with no results.

[v-ljerr-msft · Accepted solution]

Hi @Onaiggac,

After some research, I found the following two solutions on this issue. Could you go to check if it works in your scenario? 

Solution 1:

In our case it helped when we changed regional settings on PC to match format in SQL DB: Control Panel > Clock, Language, and Region > Region, Change date, time or number formats:

• Region format changed to English US (was Finland)
• Short date format changed to 'yyyy-MM-dd', matching Azure SQL DB format (was dd.MM.yyyy)

After these changes, launched PowerBI and re-connected to Azure SQL DB via direct query. Now filtering based on date column works as expected.

---

Solution 2:

Cause 

This error is due to the way different languages represent and parse dates in the SQL Server. If English is set as your default language, the SQL Server expects dates in MM/DD/YYYY format. Other languages may expect a DD/MM/YYYY format. The SQL Server will throw this error in the event of a mismatch between what is provided and what the SQL Server is expecting.

Resolution

1. On your SQL Server, open SQL Server Management Studio. Click Start > All Programs > Microsoft SQL Server > SQL Server Management Studio.
2. Using sa credentials, log in to the SQL Server instance hosting your Orion database.
3. Expand Security > Logins.
4. Right-click the SolarWinds Orion user, and then click Properties.
5. In the Select a page pane, click General.
6. In the Default language field, select English.
7. Click OK.

---

Regards

[Onaiggac]

Hi.

The main problem is PowerBI build query with dates in YYYY-MM-DD format and then executes a CAST to datetime.

My user is set, by default langue, to treat date in YYYY-DD-MM. So the Solution 2 solve my problem.

PowerBI should build querys with dates in YYYYMMDD format without "-" because it is the ISO standard.

Tks alot.

Now I have another problem. 🙂