Forum Discussion
Dynamic % return calculation based on selected date
Dear PBI users,
I've recently started using Power BI Desktop and after a few more or less successful simple visualizations I've stucked with something more complex. My inputs are closing share prices for several companies (the same date range for each).
What I need to do right now is to present % return for every day in a date range (I mean (Closing price at Day X,Y,Z divided by the first Closing price in a selected date range)-1). For example if the first day is 12 January 2017 (share price is 10) and days X and Y are let's say 20 January 2017 (share price is 12) and 29 January (share price is 13) I would like to get simple arithmetic return of 20% and 30% respectively.
It would be fantastic if I could adjust first date using a slicer (e.g. change from 12 January 2017 to other date) and arithmetic return would recalculate (for each date in a date range) using new "first date".
At the end of the day I would like to present the line chart with returns for all companies for a selected date range.
Please find below the snapshot of something that I would like to achieve.
I would be really grateful for any suggestion.
12 Replies
Dear All,
I found a quick measure that is pretty close to what I am looking for:
Closing price % difference from 2017-01-12 =
VAR __BASELINE_VALUE =
CALCULATE(
SUM('sheet1'[Closing price]);
'sheet1'[Date] IN { DATE(2017; 1; 12) }
)
VAR __MEASURE_VALUE = SUM('sheet1'[Closing price])
RETURN
IF(
NOT ISBLANK(__MEASURE_VALUE);
DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE; __BASELINE_VALUE))
As you may see the major drawback of this solution is that I can't change the date of reference (2017-01-12 in this case). Could you help me to make this formula date-flexible?
Thanks in advance!
Pete37,
Create a calendar table using DAX below.date = CALENDAR(DATE(2017;1;1);DATE(2018;12;31))
Create the following measures in your sheet1 table.selectedvalue = MAX('date'[Date])
BASELINE_VALUE =
CALCULATE(
SUM('sheet1'[Closing Price]);
FILTER(ALL('sheet1'[Date]);'sheet1'[Date]=[selectedvalue]
))MEASURE_VALUE = SUM('sheet1'[Closing Price])
Closing price % difference = DIVIDE([MEASURE_VALUE] -[BASELINE_VALUE];[BASELINE_VALUE])
And please note that use date field in the calendar table to create slicer.
Regards,Lydia
