In this Power BI article, we will discuss how to use the What if Parameter Power BI Date in the Power BI report.
What if Parameter in Power BI Date
Let us see how we can filter the Power Bi report data dynamically using the What If Parameter date in Power Bi.
In this example, we are going to use the sales table data, to filter data based on the date value using what-if parameter slicer visuals in Power Bi.
- Open the Power Bi desktop, and load the data into it using the get data option. Once the data has been loaded, select the table visually and select the order date and choose year and month fields and also drag and drop the quantity as below:
- Now Click the Modeling tab in the top ribbon and select the What IF parameter option.
- A What if parameter pop window appears, In the Name section we can enter the parameter name as Dynamic Year.
- In the Data type, we can select the data type as a whole number. We can also set Minimum value, Maximum value, Increment, and Default value.
- To save the What-if parameter click on the ok button below:
- Under the Fields pane, we can see that the Parameter has been created as a New Table
- Once the parameter has been created. It automatically creates two measure values for the generated series and for the selected value
For generated series:
Dynamic Year = GENERATESERIES(0, 12, 1)
For Selected Value:
Dynamic Year Value = SELECTEDVALUE('Dynamic Year'[Dynamic Year], 3)
- Create a new measure and apply the below Power Bi Dax formula to find the dynamic value for the selected year or month.
Dynamic Date Value = CALCULATE(SUM(Sales_Table[Quantity]),DATESINPERIOD(Sales_Table[Order Date],LASTDATE(Sales_Table[Order Date]),- 'Dynamic Year'[DynamicYearValue],YEAR))
- Dynamic Date Value = Measure Name
- Sales_Table = Table Name
- Quantity, Order Date = Column Names
- DynamicYearValue = Parameter Value
- YEAR = Interval
- Now select the card visual from the visualization and drag and drop the newly created measure value in it.
- By default, it displays the 3 years interval Quantity in the card’s visual.
In the same way, if we choose the parameter value as 2, it calculates the quantity value for the 2 years interval period as below:
This is how to filter the Power Bi report data dynamically using the What If Parameter date in Power Bi.
In this Power BI article, we have discussed how to use the What if Parameter Power BI date in a Power BI report.
You may like the following Power BI tutorials:
- What if Parameter in Power BI filter
- How to sort slicer by measure in Power BI
- Power BI Slicer Sort Descending
- How to sort slicer by another column in Power BI
- What if Parameter in Power BI
I am Bijay a Microsoft MVP (10 times – My MVP Profile) in SharePoint and have more than 17 years of expertise in SharePoint Online Office 365, SharePoint subscription edition, and SharePoint 2019/2016/2013. Currently working in my own venture TSInfo Technologies a SharePoint development, consulting, and training company. I also run the popular SharePoint website EnjoySharePoint.com