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...
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

Anonymous

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

  • Pete37's avatar
    Pete37
    Frequent Visitor
    8 years ago

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

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

        Anonymous

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