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.
Hello, begginer user here!
I want to create a line chart with the every day returns of stock prices, the problem for me is that I want it to by dynamic, being able to chose the initial and the end date of analysis.
The chart in excel and data prices look like this:
F | GM | FCHA.MI | |
Timestamp | Trade Close | Trade Close | Trade Close |
04/07/2018 | 10.99 | 38.97 | 16.21 |
03/07/2018 | 10.99 | 38.97 | 16.33 |
02/07/2018 | 11.10 | 39.50 | 16.34 |
01/07/2018 | 11.07 | 39.40 | 16.33 |
30/06/2018 | 11.07 | 39.40 | 16.33 |
29/06/2018 | 11.07 | 39.40 | 16.33 |
28/06/2018 | 11.28 | 40.52 | 16.04 |
27/06/2018 | 11.42 | 40.37 | 16.62 |
26/06/2018 | 11.52 | 41.01 | 16.44 |
25/06/2018 | 11.50 | 40.61 | 15.99 |
24/06/2018 | 11.65 | 41.25 | 16.50 |
23/06/2018 | 11.65 | 41.25 | 16.50 |
Thanks!
Solved! Go to Solution.
Hi @CharlesRondo,
In your scenario, you may need three or more date tables due to the start dates are different and independent. Please check out the demo in the attachment. Three measures are like below. (Please note: only F and GM are the same with your requirements here. You can adjust the FCA yourself.)
F = VAR minDate = CALCULATE ( MIN ( Table1[Timestamp] ), ALLSELECTED ( Table1[Timestamp] ) ) RETURN IF ( MIN ( Table1[Timestamp] ) = minDate, 0, DIVIDE ( SUM ( Table1[Trade Close] ), CALCULATE ( SUM ( Table1[Trade Close] ), 'Table1'[Timestamp] = minDate ) ) - 1 )
GM = VAR minDate = CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) ) RETURN IF ( MIN ( Table1[Timestamp] ) <= minDate, 0, DIVIDE ( SUM ( Table1[Trade Close .1] ), CALCULATE ( SUM ( Table1[Trade Close .1] ), 'Table1'[Timestamp] = minDate ) ) - 1 )
FCA = VAR minDate = CALCULATE ( MIN ( Table1[Timestamp] ), ALLSELECTED ( Table1[Timestamp] ) ) RETURN IF ( MIN ( Table1[Timestamp] ) = minDate, 0, DIVIDE ( SUM ( Table1[Trade Close .2] ), CALCULATE ( SUM ( Table1[Trade Close .2] ), 'Table1'[Timestamp] = minDate ) ) - 1 )
Best Regards,
Dale
Hi,
You can use a Date Slicer that will help you dynamically get the Dates of interest
Like the one below?
Hi @CharlesRondo,
You can use a Slicer with date field. Please refer to the snapshot below.
Best Regards,
Dale
Thanks for the reply... but, well that's just part of the process, the real issue is that I need to get the returns depending on what I put on the slicer, in other words, if I'd like to graph, lets say one day of returns, what we would do in excel is=
% Return of X in date 1= (Trade Close Price at date 1 / Trade Close Price previous day) -1
Then you graph:
Return at date X0= 0 % (First number to graph is alway cero)
Return at date X+1= (Trade Close Price at X+1 / Trade Close Price at date X0 ) - 1
Return at date 2= (Trade Close Price at date X+2 / Trade Close Price at date X0 ) - 1
Return at date 3= (Trade Close Price at date X+3 / Trade Close Price at date X0 ) - 1
And so on...
Note that the base price is always fixed, and I need to be able to chose that with the slicer because it can start at any date.
The following table shows daily returns at a specific date or price base... and then I need graph this
Hi @CharlesRondo,
In your scenario, you may need three or more date tables due to the start dates are different and independent. Please check out the demo in the attachment. Three measures are like below. (Please note: only F and GM are the same with your requirements here. You can adjust the FCA yourself.)
F = VAR minDate = CALCULATE ( MIN ( Table1[Timestamp] ), ALLSELECTED ( Table1[Timestamp] ) ) RETURN IF ( MIN ( Table1[Timestamp] ) = minDate, 0, DIVIDE ( SUM ( Table1[Trade Close] ), CALCULATE ( SUM ( Table1[Trade Close] ), 'Table1'[Timestamp] = minDate ) ) - 1 )
GM = VAR minDate = CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) ) RETURN IF ( MIN ( Table1[Timestamp] ) <= minDate, 0, DIVIDE ( SUM ( Table1[Trade Close .1] ), CALCULATE ( SUM ( Table1[Trade Close .1] ), 'Table1'[Timestamp] = minDate ) ) - 1 )
FCA = VAR minDate = CALCULATE ( MIN ( Table1[Timestamp] ), ALLSELECTED ( Table1[Timestamp] ) ) RETURN IF ( MIN ( Table1[Timestamp] ) = minDate, 0, DIVIDE ( SUM ( Table1[Trade Close .2] ), CALCULATE ( SUM ( Table1[Trade Close .2] ), 'Table1'[Timestamp] = minDate ) ) - 1 )
Best Regards,
Dale
OMG! That´s it!, works perfectly!...I need to study all the logic with the language behind since I'm more used to excel functions. Thanks!
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |