How to get corresponding price of the smallest date chosen in date slicer in Power BI

1
votes

I am quite new to working with DAX and Power BI so please don't judge. My problem seems (and might be) simple. Anyways, here we go:

I have a dataset that contains 3 colulmns: Date (date), Price (float), Performance (%)

Attribute descriptions: Date and Price are constants that are pulled from an external data source. Performance is a variable of the price change over time in percent. It is the percentage change of the price of the current date to the first date in the time-series selection (Selected "from date" of date slicer visual).

I want to create a dynamic line chart that shows performance over time. Difficulty here is when I change the "from date" I want the performance to be variable. Meaning, the price of the chosen "from date" is the new base price and should be calculated accordingly.

Formula: Date = t, price at date t = pt, performance at date t = pert

Date range: 1.1.2000 to 31.12.2010

Initial situation when "date from" in the date slicer visual = 1.1.2000:

  • t0 = 1.1.2000
  • pt0 = 5,00
  • pert0 = 0%

  • t5 = 6.1.2000
  • pt5 = 5,054
  • pert5 = (pt5-pt0)/pt0 = 1.08%

After changing date slicer so that "from date" is now 10.10.2009:

  • t0new = 10.10.2009
  • pt0new = 9,938
  • pert0new = 0%

  • t5new = 15.10.2009
  • pt5new = 9,832
  • pert5new = (pt5-pt0)/pt0 = -1,05%

As described, I want whatever is selected as starting point from the date slicer as the new base value for the performance calculation and the line chart should adjust accordingly.

I know how to do the dynamic line chart but I cannot figure out the measures and calculated columns I need to do so.

Any help is very much appreciated!

Cheers, MLU

1
chartspowerbivisualizationdaxpowerbi-desktop

1 Answers

1
votes
  1. Calculate the benchmark as the price associated to the first date in the period. SELECTEDVALUE assumes you have one price per Date, otherwise use an aggregator (e.g. MIN, MAX, AVERAGE). I use ALLSELECTED so the Benchmark is affected only by Filter Context (slicers) and you can easily use it in visualizations that change the context.
  2. Save our benchmark in a variable for later use
  3. Divide each price by the benchmark. Here we need to apply an aggregator to the Price, I used AVERAGE assuming you have only one Price per day, therefore, the result is the price itself.

Here is the measure:

Price vs Dynamic Benchmark :=
VAR vbenchmark = CALCULATE(SELECTEDVALUE(Dataset[Price]),FILTER(ALL( Dataset[Date]), Dataset[Date] = CALCULATE(min(Dataset[Date])), ALLSELECTED(Dataset))
return
AVERAGE(Price) / vbenchmark