Date Range for Paginated Report Parameter using Calendar Format

We have Power BI Report Builder to create paginated reports from Power BI Datasets and other popular data sources. We also have the Power BI Service, where we can publish paginated reports to Premium-backed workspaces and export them to PDF and Microsoft Excel formats.

Now, let’s look at the date filtering problem we faced.

Date Pickers and Date Dimensions

It’s not uncommon to be able to filter a report between two dates – that is exactly what we wanted to achieve with our client! Furthermore, since the Power BI Dataset contained a date dimension, we wanted to use that to ensure that the model was filtered correctly depending on the date ranges specified. Other people have done a great job of explaining how to use Data Pickers in SSRS – in fact, there is a great explanation here. This was written because we use Analysis Services Multidimensional Models and, therefore, Multidimensional Expressions (MDX) to query the cube. However, we know that Power BI utilizes Tabular Models as opposed to Multidimensional Models, and therefore, Data Analysis Expressions (DAX) is used to query the dataset, as opposed to MDX. So, how did I manage to use the Date Picker with a Power BI Dataset?

Step- Open Power BI Report Build

Step- Click on Blank Report.

Step – Load a Power BI Dataset into Power BI Report Builder from the Data option. (Pre-existing Dataset can be used for creating a Paginated Report).

Step – We loaded (Parameter Test) Dataset to create a Paginated Report.

Step – Give the name as needed.

Step – Click on Query Designer.

Step – Select the required fields from Measure Group.

Step – Drag the Required Fields.

Step – Use this for passing the parameter.

Dimension – Date Hierarchy

Hierarchy – Date

Operator – Range (Inclusive)

Parameter – check both the options for (FromDateHierarchy, ToDateHierarchy)

In the Dataset (Testing_Dataset) is available. In the Parameter, two Parameter are Created (FromDateHierarchyDate, ToDateHierarchyDate)

Step – Right-click on the Testing_Data and Choose Query Option to see the Dax expression for the dataset.

Step – Click on the Insert option. Select Insert Table.

And drag the required fields from the dataset into the table visual. (Invoice, Sale, Date).

Step – Click on the Run option. To run the report.

An error will pop up. This is because the datatype of (FromDateHierarchy, ToDateHierarchy) parameter is Data/Time.

Follow the below steps for a better understanding.

Step – Click on Design to go back.

Step – Right Click on fromDateHierarchyDate and Select Parameter Properties.

Step – Change the Datatype to Text. Follow the same for ToDateHierarchyDate

Step – Again, “Run” the report.

Now, the Report will Come up. But the Data Type of the From Date and To Date is Text.

Step – Click on Dataset to show the hidden dataset.

Step – Delete the FromDateHierarchyDate and ToDateHierarchyDate Dataset. By right click and select Delete.

These Datasets are automatically created when we create parameters, and these are connected with parameters.

Step – Right Click on FromDateHierarchyDate and select Report parameter properties.

Click on Available Values and Select “None”

Follow the Same for ToDateHierarchyDate.

Step – Right Click on the Testing_Dataset and Select Query.

By default, In the Dax, the Var vfromDatahierarchyDate1 is treating the fromDate as a text and doing the whole Text manipulation, and the same for To Date.

Step – We are changing the datatype of Variable vfromDateHierarchyDate1 from Text to Date. By using the DateValue Function and following the same for Variable vToDateHierarchyDate1, we need to convert the Datatype from Text to Date.

Deleted the unwanted parameter from Dax.