PowerBI - Trying to populate a column with indexed data.

[Ruairi_O]

Hi all, 

I want to convert a column fund closing prices to an index. Starting at 100, but not sure how to approach it with DAX. 

I have an imported excel file on my PowerBi Data View tab called Sheet3. It has 3 columns Date, Fund A, Index.

I added a 4th column in PowerBi called Fund A Index. I want to set a start date (in this case 31/12/2021) and store it in a variable as the start date could change. 

Fund A Index =

VAR ClassStartDate = Sheet3[Date] = DATE(2021, 12, 31)

RETURN IF(ClassStartDate, 100, BLANK())

That part is fine start date value = 100.

Any date after that it should vidide the corresponding value in the Fund A column by the value in Fund on the row of ClassStartDate (in this case 31/12/2021) and multiply by 100. 

basically new/old * 100 So in the below example for 03/01/20222 = 165.34 / 165.5 * 100 = 99.90 and populate that value in the Funa A Index equivalent row. 

After that 04/01/2022 165.4 / 164.5 * 100 = 100.55 and populate that the Fund A Index.

And so on and so on. 

How can I go about this. 

[Ruairi_O]

Thanks Ahmed! 

I'm going to reverse engineer line 3 to learn how it exactly works. Thansk so much! 

BR,

Rory

[Ahmedx]

pls try this