How to Filter Tabular Data for Today and the Previous Two Years in WPS Office Excel
Question details
The user needs to create a dynamic date filter in a tabular query or model so it always includes rows from today through exactly two years earlier, without editing hard-coded dates.

- Product
- WPS Office Excel / Power Query
- Device & OS
- not provided
- Scenario
- The user is building or editing a tabular model query and must restrict a date column to the current date and the date two years before today.
- Observed behavior
- Date functions such as TODAY or DateTime.LocalNow return errors or fail when used in the wrong query language or against a date column stored as text.
Confirm the type of your date column and the query language you are using before changing the filter. For SQL-backed sources, use native SQL date functions; for Power Query, use M date functions.
Use Power Query M to Filter Rows Between Today and Two Years Ago
Use this when the tabular data is loaded through Power Query in WPS Office Excel and you need a rolling two-year window without hard-coded date values.
This method avoids Excel TODAY and SQL syntax conflicts by using Power Query M functions that evaluate the current date at refresh time.
In WPS Office Excel, select the data range or table, go to the Data tab, and click From Table/Range to open the Power Query Editor.
Click the date column header in the Power Query Editor, open the Data Type dropdown on the Home or Transform tab, and choose Date or Date/Time so the column is not treated as text.
On the Home tab, open Advanced Editor or add a custom column, and define today as Date.From(DateTime.LocalNow()) and start as Date.AddYears(today, -2).
Select the date column, click the filter arrow, choose Date Filters, and then Between. Set the bounds to the computed start date and today from the query formulas.
Click Close & Load on the Home tab to load only the rows from two years ago through today into WPS Office Excel.

Filter a SQL Tabular Source with DATEADD and GETDATE
Use this when the tabular model is backed by a SQL database and you can write or edit the native query.
Create a Rolling Two-Year Date Filter in WPS Office Excel Power Query
You can build the dynamic filter directly in WPS Office Excel by using Power Query M functions to evaluate today's date and the date two years earlier, then load only the rows within that range. This avoids hard-coded dates and refreshes automatically.
- 1. Open WPS Office Excel and load your table: Launch WPS Office Excel, open the workbook or CSV, select the table, and go to Data > Get Data to open the Power Query Editor.
- 2. Set the date column to a real date type: Right-click the date column in Power Query Editor and choose Change Type > Date or Date/Time to prevent text-based date errors.
- 3. Create the rolling date variables in M: In Advanced Editor or a custom column, enter today = Date.From(DateTime.LocalNow()) and start = Date.AddYears(today, -2).
- 4. Filter between the two computed dates: Click the date column filter arrow, choose Date Filters > Between, and set the bounds to start and today.
- 5. Close & Load to refresh: Click Close & Load on the Home tab to return the filtered result to WPS Office Excel.

Frequently Asked Questions
Why does my filter fail when I use TODAY() in Power Query?
Power Query uses M language, not Excel worksheet functions. In WPS Office Excel, open the Power Query Editor, select the date column, and use Date.From(DateTime.LocalNow()) for today instead of TODAY().
How do I check if my date column is text instead of a real date?
In Power Query Editor, select the column and look at the data type icon in the column header. If it shows Text, right-click the column and use Change Type > Date or Date/Time before applying date filters.
Can I use DATEADD and GETDATE for a SQL tabular model?
Yes. If your tabular source is SQL Server, open the native query or connection in WPS Office Excel, then use WHERE Order_Date >= DATEADD(Year, -2, CAST(GETDATE() AS date)) AND Order_Date <= CAST(GETDATE() AS date).
Does WPS Office support Excel Power Query date filters?
WPS Office opens .xlsx workbooks and provides data import and transformation tools. You can edit the query in the Power Query Editor and apply a rolling date filter using M functions.
What is the Power Query formula for a two-year rolling window?
In Advanced Editor, define today as Date.From(DateTime.LocalNow()) and start as Date.AddYears(today, -2), then filter the date column between start and today.




