Help with defining starting row value

[tnevarez]

Hello!  I am new to the forum and am stuck trying to come up with a formula/measure.  I am needing to index 3 different data sets in order to align them on the same scale instead of graphing with multiple axis.  I know what formula I need to be using but the issue I am having is defining the initial value.  I want to be able to use a filter slicer to graph different date ranges of the data so the initial value would change depending on the date selected.   You can see from the 2 different charts (modeled in excel) that even with a secondary axis the data sets are too different to plot effectively (item 3 is too small to plot).  By indexing the values, I am able to narrow the scale to show the variations better.

The formula I need is: (Current value/Initial value)*100

Any help would be GREATLY appreciated; thanks!!

[tnevarez]

Thank you for the quick reply! 

Based on the lookup value needed I am going to have to do some pre-work to get Items 1, 2, & 3 into a column within one of my data tables since they are currently measures.  I have a master data table that pulls in values for Items 1-~100 in a single column with each row a daily date.  I had to create the measures to get a calculation for Item 1 (for example, using Items (5+10+13+20) - 18, and then additional measure for Items 1, 2, & 3 to calculate the weekly averages.  So currently even though all of the values reside in my master data table, I had to create the measures for the desired results.  Once I can get them into a table to perform the lookup value, I will be able to try your solution.

Thanks!

[jgeddes · Accepted solution]

You can create three measures (one for each Item) with the following...

Item 1 Indexed Value = 
var _initialValueDate =
//gets the minimum week end date from the table for the dates that are SELECTED(Filtered)
MINX(
    ALLSELECTED('Table'),
    'Table'[Week End Date]
)
var _initialValue =
//gets the Item 1 value that coresponds to the intial date calculated
LOOKUPVALUE(
    'Table'[Item 1],
    'Table'[Week End Date],
    _initialValueDate
)
return
//returns the indexed value using the inital value calculated from the initial date
ROUND(
    DIVIDE(
        MIN('Table'[Item 1]),
        _initialValue,
        0
    )
    *100,
    0
)

You should end up with...

[tnevarez]

Thank you again for the solution!  I was finally able to dedicate time to get all these measures moved into a single table to perform the lookup function and your formula works perfect!