2022 Week 03 | Power BI: Can you visualize percent difference from a selected date?

13 Comments / Power BI, Workout Wednesday / By

Introduction

This week we are taking a page from the Tableau team and looking at how a metric has changed from the value at a specified reference date. 

Requirements

  1. Create a line chart showing percent change of the close price from a selected date. 
  2. Use a slicer or other visual to allow users to change the reference date. 
  3. Use conditional formatting to display the selected reference date in the title of the line chart. 
  4. Instead of a legend, display the series label at the end of each line. 
  5. Add a vertical reference line to the line chart that marks the selected reference date. 

Dataset

This week’s data comes from Yahoo Stocks API. We are using the data that Lorna collected and made available on Data.World. 

Owner: missdataviz
Dataset ID: wow22w3-stocks-jan22

Share

After you finish your workout, share on Twitter using the hashtags #WOW2022 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd, @KerryKolosko, and @NerdyWithData. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year. 

Solution

Download PBIX here. 

13 thoughts on “2022 Week 03 | Power BI: Can you visualize percent difference from a selected date?”

  1. Kevin

    Managed to get all other things except the DAX code to
    1. keep the lines unfiltered when a date is picked in the slicer. Currently, when I pick a date all the lines are getting filtered.
    2. DAX to work out percent change of the close price from a selected date.

    Wondering is below DAX valid to compute the monthly % change of close prices?
    Monthly Close % Chg =
    VAR PrevMonthClose =
    CALCULATE ( SUM ( Stocks[Close] ), DATEADD ( Stocks[Date], -1, MONTH ) )
    VAR CloseVal =
    CALCULATE ( SUM( Stocks[Close] ), Stocks[Date] = SelDate )
    RETURN
    DIVIDE ( CloseVal – PrevMonthClose, PrevMonthClose )

  2. Meagan Longoria

    Hi, Kevin. The PBIX and solution video are available now, so that should help. Your DAX is pretty close, but the exercise is calculating difference from the reference point instead of the previous month. Here is what I used.
    % Change Close =
    Var CurClose = Max('Stocks'[Close])
    Var SelectedDate = SELECTEDVALUE('Reference Date'[Date])
    Var RefClose = CALCULATE(Max('Stocks'[Close]), 'Stocks'[Date] =SelectedDate)
    RETURN
    Divide(CurClose - RefClose, RefClose)
    For the date selection part, you’ll need to use a disconnected table.

  3. Kevin

    Many thanks, Meagan for the DAX. I was able to replicate the same in my file. Hoping to learn about more such clever tricks.. thanks again.

  4. Dru

    Hello Meagan, thank you for the informative video. Can i ask if this method will work with Year rather than a date? I’m just having trouble getting a year version to work correctly.

  5. Dansel

    Hello and many thanks to you, Meagan
    I just need to have my stocks returns for the last 15 day period, How can I modify your code to get that done?

  6. Meagan Longoria

    I’m not sure if you mean the last 15 days from the current date or something like the 15-day rolling average close price. If the former, instead of a reference date, you will want to add a regular date table and create a relationship between the Stocks table and Dates table. Then you can add a slicer and change it to relative date and enter 15 days. If you are looking for more of a rolling average, you can find helpful DAX at https://www.daxpatterns.com/standard-time-related-calculations/

  7. Meagan Longoria

    I’m not sure what you want when you say “work with year”. If you have a date table connected to your fact and can aggregate your data at the year level, then you can make this work.

  8. Dansel

    thanks for the reply, I don’t need the rolling ave, I just need a percentage change from a specified date 15 days back (either from the current date or any date). point to point was what i meant. thank you again.

  9. Jeff

    Hi Meagan- great stuff and appreciate you sharing this Dax. I am trying to run the same dax but the dates for each set are not always aligned. These are Home Starts. One house may have dates 1/10/23, 1/13/23,1/22/23 and another has 1/3/23, 1/13/23, 1/24/23.

    Selecting a date meets me with only one data set unless I select a date in both categories.

    Is there a method to have a selected date in the slicer, then use the next largest data point for each category as the zero point?

    E.g. using my sample above, if I select 1/12/2023, the zero point will display at 1/13 for both. If I select 1/21, the zero point will display at 1/22 and 1/24 for their respective line.

  10. Meagan Longoria

    Instead of using the original sums to calculate percent change, you would have to do something like a semi-additive measure for the initial inputs, although it sounds like you want to use min date instead of max date.

  11. Wenjun S

    Hello Meagan,

    Thank you for posting this challenge. I followed your video instructions, and when I selected a reference date in the slicer, the visual only showed the percent change for the selected date ( four points instead of four lines). Can I ask what might cause that issue?

    Thank you!

  12. Meagan Longoria

    Not sure I can diagnose that off of one sentence. Check to make sure you don’t have any relationships between the Reference Date table and the Stocks table. If that’s not it, there is likely a problem in your DAX measures.

  13. Wenjun S

    Thank you for your response, Meagan! It turned out it was the date hierarchy that casued the issue.

Comments are closed.

Scroll to Top