Forum Discussion

Pete37's avatar
Pete37
Frequent Visitor
8 years ago

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 price...
Pete37's avatar
Pete37
Frequent Visitor
8 years ago

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!