Today we are going to tackle what is arguably the most annoying part of working with paginated reports: getting that date picker to work with your report parameter.

Let’s dive in!

A date picker is a user-friendly pop-up calendar that lets you select a date or range. If you’re familiar with Power BI Desktop, you know how easy it is to create a slicer for a date field and choose between options. However, when you’re developing a paginated report, the process becomes more complex—but it’s still doable.

• When you go into the query designer to add a parameter, start by defining the date table and the date field.
• Select “range inclusive” as the operator to include the start and end dates specified by the user.
• Make sure to click the parameter boxes for both the start and end dates. If you only click one, the other will default to a filter, which might not be what you want.
• After validating the query and clicking OK, you might think you’re done—but you’d be wrong. When you test the report, it often fails, leaving you frustrated. To understand why this happens, let’s look at what goes on behind the scenes.

Power BI Report Builder sets up a sort of parameter relay race. It establishes “from” and “to” date parameters and defines their data types as DateTime, which is what we need for the date picker. However, it also creates a hidden dataset that connects to these parameters and sets them to available values.

The error occurs because the DAX query script uses formatted date strings, which conflict with the DateTime data type required by the date picker. To fix this:

1. Set the DateTime Type: Ensure the data type for the report parameter is DateTime.
2. Remove Hidden Datasets: Get rid of the hidden datasets to avoid confusion during troubleshooting.
3. Set Default Values: Default your values to something useful, like today’s date.
   – For the query, we’ll set up a format expression to define the date string formatted as “yyyy-MM-dd”. This might seem counterintuitive, but it works.

Here’s how it looks in practice:

1. Select Your Date Table and Field: Choose your date table (dimension) and date field/hierarchy.
2. Set Parameters: Choose “inclusive range” and validate the query. Ensure the data type is DateTime and clear out the available values.
3. Default to Last Year: Use the DateAdd function to subtract 365 days from today’s date for the default “from” date. For the “to” date, set it to today.
4. Format Date Strings: Format the dates into “yyyy-MM-dd” text strings to feed into the query.

After setting this up, you’ll have a functional report with a date picker that defaults to the past year and today’s date. End users can adjust the dates as needed.

I hope this guide makes your next paginated report development process a bit smoother. If you have any questions, please leave them in the comments below.