- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 🙂
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for the quick response - however, I would need it in the query where data is extracted so that i can limit the dataset. 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
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.
Subject | Author | Posted | |
---|---|---|---|
07-12-2024 05:52 AM | |||
07-22-2024 11:52 AM | |||
07-10-2024 05:40 AM | |||
03-14-2024 12:55 PM | |||
07-12-2024 02:32 PM |
User | Count |
---|---|
105 | |
75 | |
44 | |
39 | |
33 |
User | Count |
---|---|
165 | |
90 | |
65 | |
46 | |
43 |