10. Parameters in Power Query (and Other Kinds of Parameters)
10.5 Date-range Parameters for Filtering
Start with a problem
Amir's training laptop is slow when it loads every order. He only needs May 2026.
Explain simply
Create StartDate and EndDate parameters (type Date) and filter the Order Date column with them. Only those rows are loaded.
Show the data
| Order ID | Order Date | City | Amount (₹) |
|---|---|---|---|
| 301 | 06-04-2026 | Pune | 400 |
| 302 | 06-04-2026 | Pune | 200 |
| 303 | 07-04-2026 | Nashik | 300 |
| 304 | 04-05-2026 | Nashik | 150 |
| 305 | 04-05-2026 | Kolhapur | 450 |
| 306 | 05-05-2026 | Pune | 250 |
| 307 | 05-05-2026 | Nashik | 350 |
| 308 | 05-05-2026 | Kolhapur | 0 (cancelled) |
About this data: This table uses columns: Order ID, Order Date, City, Amount (₹). This table uses columns: StartDate, EndDate, Orders loaded, Total Amount (₹). This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv (source: src/practice/).
Demonstrate
- Create
StartDate(Type Date, current value 01-05-2026) andEndDate(Date, 31-05-2026). -
Orders › Order Date filter › Date Filters › Between… › for each box choose Parameter › StartDate and EndDate › OK.
Your result should look like this:
Rows kept 304, 305, 306, 307, 308 (5 rows) -
If the column is Date/Time, compare like with like:
DateTime.From(StartDate), or convert the column to Date first (code below). - Close & Apply and check the card.
-
Change StartDate to 06-04-2026 and EndDate to 07-04-2026 › Apply changes.
Your result should look like this:
Rows kept 301, 302, 303 (₹900)
Show the result
Your result should look like this (fictional sample):
| StartDate | EndDate | Orders loaded | Total Amount (₹) |
|---|---|---|---|
| 01-05-2026 | 31-05-2026 | 5 | 1,200 |
| 06-04-2026 | 07-04-2026 | 3 | 900 |
Explain what happened
The filter step reads the parameters at refresh, so only those rows reach the model. With a SQL source, the filter folds into a WHERE clause.
Explore further
- Between is inclusive at both ends for Date columns. For Date/Time columns, use
< Date.AddDays(EndDate, 1)so the last evening is not lost.
Detailed reference for this lesson:
Loading 5 years of orders on a training laptop is slow. A StartDate parameter limits the rows, and with SQL sources the filter folds into the SQL WHERE clause.
Steps in Power BI
- Create
StartDate(Type Date, current value 01-01-2025) and optionallyEndDate. - In Orders, click the Order DateTime filter arrow › Date/Time Filters › Custom Filter (or Is After or Equal To). In the value box click the drop-down › Parameter ›
StartDate. - Because Order DateTime is Date/Time and StartDate is Date, adjust the formula (below) to compare like with like.
- Right-click the step › View Native Query to confirm the
WHEREclause (Module 4.4).
Filtered = Table.SelectRows(Orders, each
[OrderDateTime] >= DateTime.From(StartDate) and
[OrderDateTime] < DateTime.From(Date.AddDays(EndDate, 1)))
Tip
A DevMode True/False parameter can switch between a small sample and the full data: if DevMode then Table.FirstN(Filtered, 5000) else Filtered. Remember to set it to false before publishing.
Ravindra Bagale's Tip
Friends, many students filter with a date parameter on a text column and get no rows or errors. Make sure the column and the parameter have the same type (Date vs Date/Time). For incremental refresh, use Date/Time and filter with >= RangeStart and < RangeEnd so no row is counted twice. Try it once more, then move on.
Ravindra Bagale's Tip – मराठी
मित्रांनो, बरेच students text column वर date parameter ने filter लावतात आणि त्यांना एकही row मिळत नाही किंवा errors येतात. Column आणि parameter चा type सारखा आहे याची खात्री करा (Date vs Date/Time). Incremental refresh साठी Date/Time वापरा आणि >= RangeStart आणि < RangeEnd ने filter लावा, म्हणजे कोणतीही row दोनदा मोजली जाणार नाही. पुन्हा एकदा करून बघा, मग पुढे जा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students text column पर date parameter से filter लगाते हैं और उन्हें कोई row नहीं मिलती या errors आते हैं. पक्का करो कि column और parameter का type एक जैसा हो (Date vs Date/Time). Incremental refresh के लिए Date/Time इस्तेमाल करो और >= RangeStart और < RangeEnd से filter लगाओ, ताकि कोई row दो बार न गिनी जाए. एक बार फिर करके देखो, फिर आगे बढ़ो.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Orders from the evening of 31-May are missing | Date/Time compared with <= EndDate (midnight) |
Use < Date.AddDays(EndDate, 1) |
| The card still shows all months | Fixed dates typed in the filter instead of the parameter | Choose Parameter in the filter dialog |
| Expression.Error: cannot compare DateTime and Date | Types differ | DateTime.From(StartDate) or DateTime.Date([Order DateTime]) |
Practise independently
Practice task
Set StartDate and EndDate both to 04-05-2026. Which orders load, and what is the total?
Hints
- Only one date is in range.
Solution
304 (₹150) and 305 (₹450): 2 orders, ₹600.
Think like a professional
A date-range parameter limits what is loaded; a slicer limits what is shown. For large models use incremental refresh (10.8) instead of asking people to edit parameters.
Revision card