Dynamic % return calculation based on selected date

[Pete37]

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.

[v-yuezhe-msft]

@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

[Pete37]

@v-yuezhe-msft

Thanks for your response. I am afraid that I did something wrong because it doesn't work. Please look at the screenshot below.

[Pete37]

[v-yuezhe-msft]

@Pete37，

Do you need to select data range or single date value in the date slicer? And do you use Date field of calendar table to create the slicer?

Regards,
Lydia

[Pete37]

@v-yuezhe-msft

I need a range to present it on the line chart. Yes I use Calendar Table to create the slicer.

[v-yuezhe-msft]

@Pete37,

Change BASELINE_VALUE to the following:

BASELINE_VALUE = 
CALCULATE(
SUM('sheet1'[Closing price]);
FILTER(ALL('sheet1'[Date]);'sheet1'[DATE]<=MAX('date'[Date]) && 'sheet1'[Date]>=MIN('date'[Date])
))

Regards,
Lydia

[Pete37]

@v-yuezhe-msft

The new function has too few arguments. I am not sure how to fix it.

[v-yuezhe-msft]

@Pete37,

I miss ")" after ALL() function, please check DAX below.

BASELINE_VALUE = CALCULATE( SUM('sheet1'[Closing price]); FILTER(ALL('sheet1'[Date]);'sheet1'[DATE]<=MAX('date'[Date]) && 'sheet1'[Date]>=MIN('date'[Date]) ))

Regards,

Lydia

[Pete37]

@v-yuezhe-msft

Unfortunately it still doesn't work 😞 All lines on the chart should start from 0 as at 2017-01-12

[v-yuezhe-msft]

@Pete37,

Could you please share your PBIX file to me? I will check it in my environment. And you can share your PBIX file via Private Message.

Regards,
Lydia

[Pete37]

Is there anyone else who could help me with this issue?

[Pete37]

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!