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