Normalize values based on the first one

[zivhimmel]

Hi,

I'm trying to display a graph that shows the performance of a portfolio. Currently it looks like this :

As you can see, the first value displayed is 101.63, because this is the value of the portfolio at the begining

of the selected date range.

I would like to dynamically set the first value to a 100 and adjust all values accordingly. For example, if the values

in the date range are as follows :

day 1 : 110

day 2 : 115

day 3 : 105

I would like then to be : 

day 1: 100

day2 : 104.54

day3 : 95.45

The calculation is done by dividing each of the values by the value of the first day(110), and maltiplying by 100.

I don't know how to do it in DAX.

You help is appreciated, thanks !

[OwenAuger · Accepted solution]

Hi @zivhimmel

I would do something like this:

(I'm calling your fact table Data and assuming you have a related calendar table called Calendar with the relationship on the Date column, and a base measure called [Value] )

1. Define a measure [Value First Date]
   

   Value First Date =
   /* Optional check: Only return Value First Date up to the max date that actually appears in the fact table */
   IF (
       MIN ( 'Calendar'[Date] ) <= CALCULATE ( MAX ( Data[Date] ), ALL ( Data ) ),
       CALCULATE (
           [Value],
           CALCULATETABLE ( FIRSTDATE ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
       )
   )

2. Define a [Value Normalized] measure:
   

   Value Normalized =
   DIVIDE ( [Value], [Value First Date] ) * 100

[zivhimmel]

@OwenAuger, thank you so much. Awesome solution !

[autodafe]

Excellent!

I would nee to set the value to 100 to ANY first date taken into account for analysis and then normalize subsequent values accordingly.

Is this possible in DAX?

[autodafe]

Very Nice Example!
I would need as similar method that calculatyes 100 as ANY first date taken in to account as starting date, and then normalize values accordingly. Is it possible with DAX ?