Filter Date in Query

[Anonymous]

Hi all, 

I would like to filter always last year and this year from my data source in the query. However, if i were to change the date column from text to date format (so that i can achieve that dynamic last year and this year filter) then my query would run very slow. Is there a way to extract always last year and this year while leaving the column in a text format? Any other suggestions would also be appreciated 🙂 

[amitchandak]

@Anonymous , For this year vs last year you need date , You can use a date table and create measure like

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

or

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Thanks for the quick response - however, I would need it in the query where data is extracted so that i can limit the dataset. 🙂 

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

If the text date column of your source data contains the year information, you can use the text functions to extract it out. Of course, it will be more convenient if you add a year column to your source data.

Here is an example when I use SQL Server as my data source.

SELECT [Date],[Year]
FROM [CaseTest].[dbo].[Table_2]
WHERE RIGHT(TRIM([Date]), 4) in (YEAR(SYSDATETIME ( )),YEAR(SYSDATETIME ( ))-1)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.