Power BI reporting is a great way to display large amounts of data using out-of-the-box charts and widgets. All these chart and widgets provide a lot of features like drill down, filter based on click, export, etc. But since Power BI is a reporting tool, we tend to come across a lot of data and often we would like to see data for a specific date range to start with.
Date slicer is the perfect way to achieve this functionality. With date slicer, we can provide data to and from a date and the data would be restricted to these dates. Whatever date period we set in the Power BI file in desktop would be displayed by default when the report is loads and then we can adjust the date as per our needs. But what if we want to set the date range on the date slicer as per current period? What should we do if we want to set the date slicer to show the last 90 days from the current date and the same should be shifted as the we move to next date? Usually, we either need to update the date in Power BI Desktop or adjust the date in app.powerbi.com. But it would be difficult to do that every day. This is where Power BI-JavaScript library comes into the picture.
In this article, we will be going through the steps to achieve a dynamic date range on Date slicer using Power BI JavaScript library. Below are the few requisites to achieve this functionality:
- Knowledge of Power BI report and Date Slicer
- Information regarding usage of Power BI JavaScript library
- Knowledge on any server-side technology. For this article we will be using C#.
Step 1:In the first step, we will be setting up the Date slicer on Power BI side. Let’s say we have a “DimCustomer” table and a “Date” field. We would set up the date slicer using the same, not other setting required. We have not set up a relative date range filter.
Step 2: Moving on the next part. We are going to use Power BI – JavaScript library and Power BI C# SDK for embedding. We can find more details in the below link.
Power BI – JavaScript Library: https://github.com/Microsoft/powerbi-javascript/wiki
Power BI C# SDK: https://github.com/microsoft/PowerBI-CSharp
We are not going into details of embedding. All the details can be found in the above link.
Step 3: Once the code is ready and the report is embedded, we can move on to the next step. We are going to set two dates from date and to date in C#. We want to see data for 3, so we are going to set up -90 in the AddDays function.
Step 4: Next, we will set up a filter using Power BI JavaScript library:
For more information on filters, please see the below link: https://github.com/Microsoft/PowerBI-JavaScript/wiki/Filters
Step 5: The last step is to pass the new filters to config object as below:
We are all done, so we should be able to see the date, right? But it’s strange: The date is still not reflected as expected. So, after some more digging we found that the date needs to be entered in a specific format, only then will it show up in reports.
Step 6: So, if you go to this link https://docs.microsoft.com/en-us/power-bi/service-url-filters and scroll all the way to Date Data types, we can find the date format that is expected. Once we set the date format to the expected format, everything works fine.
There we go. Now, we can set up any date range period on the Date Slicer. To change the date period, all we need to do is to set up the desired from date and to date using C# and the same would be set up in Power BI.
Be the first to comment