Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone!
I would love some help with calculating the change in percentage.
In short, my dataset consists of Nike's stock closing price during 2020, the relevant attributes are "Date" and "Close price". In my dashboard, I have tried create a card in combination with a slicer displaying "between" dates.
What I want to display is basically the change in Nike's stock price from the chosen start date to the end date in the slicer, for example between 3/1-3/7 as seen in the picture. I have tried out different measures but dosen't seem to get it right. How is this done in the easiest way?
Thanks on forehand!
Solved! Go to Solution.
Not clear if you have a Date table or not, but the approach is similar. Basically, you need to harvest the min/max dates selected in your slicer, calculate two variables (one for each), and then do the math. Here is an example that assumes you have a single table with all your data.
Pct Change =
VAR vStartDate =
MIN ( Table[Date] )
VAR vEndDate =
MAX ( Table[Date] )
VAR vStartPrice =
CALCULATE (
MIN ( Table[Close] ),
Table[Date] = vStartDate
)
VAR vEndPrice =
CALCULATE (
MIN ( Table[Close] ),
Table[Date] = vEndDate
)
RETURN
DIVIDE (
vEndPrice - vStartPrice,
vStartPrice
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@Anonymous , prefer to use a date table
diff between selected range =
var _max = maxx(allselected('Date'),'Date'[Date])
var _min = minx(allselected('Date'),'Date'[Date])
return
divide(calculate(max(table[close price]), filter('Date', 'Date'[Date] =_max)),calculate(max(table[close price]), filter('Date', 'Date'[Date] =_min)))-1
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
@Anonymous , prefer to use a date table
diff between selected range =
var _max = maxx(allselected('Date'),'Date'[Date])
var _min = minx(allselected('Date'),'Date'[Date])
return
divide(calculate(max(table[close price]), filter('Date', 'Date'[Date] =_max)),calculate(max(table[close price]), filter('Date', 'Date'[Date] =_min)))-1
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Not clear if you have a Date table or not, but the approach is similar. Basically, you need to harvest the min/max dates selected in your slicer, calculate two variables (one for each), and then do the math. Here is an example that assumes you have a single table with all your data.
Pct Change =
VAR vStartDate =
MIN ( Table[Date] )
VAR vEndDate =
MAX ( Table[Date] )
VAR vStartPrice =
CALCULATE (
MIN ( Table[Close] ),
Table[Date] = vStartDate
)
VAR vEndPrice =
CALCULATE (
MIN ( Table[Close] ),
Table[Date] = vEndDate
)
RETURN
DIVIDE (
vEndPrice - vStartPrice,
vStartPrice
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |