What if parameter Power BI Date

by

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:
What if Parameter in Power BI Date
What if Parameter in Power BI Date
  • 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:
What if Parameter in Power BI Date example
What if Parameter in Power BI Date example
  • 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)
Example of What if Parameter in Power BI Date
Example of What if Parameter in Power BI Date
  • 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))
  1. Dynamic Date Value = Measure Name
  2. Sales_Table = Table Name
  3. Quantity, Order Date = Column Names
  4. DynamicYearValue = Parameter Value
  5. 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.
What if the Parameter in Power BI Date
What if the Parameter in Power BI Date

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:

What if the Parameter in Power BI Date example
What if the Parameter in Power BI Date example

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:

>