Dynamic vertical/ Y axis line in Power BI

August 29, 2019


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


Please reload

Recent Posts

Please reload


Please reload


Please reload