In this post I will showcase how to set up dynamic vertical line in Power BI and use it to explore your dataset.
The Adventure dataset is used in this example. In the Calendar table create a new column called DateInNumberFormat as below
We will also create a Summary Orders table like below and establish a relationship between this table and the calendar table
Next create a What-If parameter called DynamicDate1 with the minimum and maximum from the DateInNumberFormat column like below and add it to the page. A new tabled DynamicDate1 with DynamicDate1 Column and DynamicDate1 Value measure will be created.
Next create a measure called DynamicDateFormat1 and place it in the same table. This will lookup the date value based on the number in the DateInNumberFormat column
Create another What-If parameter called DynamicDate2 following steps in the previous paragraph and create a new measure DynamicDateFormat2
Now that we have set up our parameter tables we will start creating new measures to use these parameters. Create two measures to create the vertical lines
The maximum orders per day in the dataset is 198 so we have chosen 300 so that the lines extend way beyond the maximum.
Next create these four measures
The logic here is to define the values at the beginning of the selected period and at the end of the selected period. If the end value is greater than the beginning value the line is formatted as green else its formatted as red. I have used a simple difference to compare values but you can you other metrics like variance, slope etc.
These measures will make sense when we place them in our visual.Place a line and stacked column chart on a report page and drag the measures so that it looks like this
Change the colour of the line for OrderQuantityGreen to Green and OrderQuantityRed to Red.
Next place the DynamicDate1 and DynamicDate2 as slicers and have them as single values and place them above the line and stacked column chart. Below them place the Card visual and drag the measures DynamicDateFormat1 and DynamicDateFormat2 into them. The final layout should look like this
Interact with the slicers and see how the middle section changes colour here