Reply
avatar user
Anonymous
Not applicable
Partially syndicated - Outbound

Filter Date in Query

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 🙂 

1 ACCEPTED SOLUTION

Syndicated - Outbound

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)

vkkfmsft_0-1642731646612.png

vkkfmsft_2-1642732126580.png

 

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.

 

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

Syndicated - Outbound

@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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
avatar user
Anonymous
Not applicable

Syndicated - Outbound

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

Syndicated - Outbound

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)

vkkfmsft_0-1642731646612.png

vkkfmsft_2-1642732126580.png

 

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.

 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)