Price Index Table Over the Time

Hi ahadtk ,

1. You can transpose the table in Power Query Editor and then add a new column to assist [Product] sorting. Like this.

2. then please create measures like:

1 Day = 
VAR _DATE_1 = MAX('Sheet1'[Date])
VAR _DATE_2 = MAX('Sheet1'[Date])-1
VAR _VALUE_1 = CALCULATE(MAX('Sheet1'[Value]),'Sheet1'[Date]= _DATE_1)
VAR _VALUE_2 = CALCULATE(MAX('Sheet1'[Value]),'Sheet1'[Date]=_DATE_2)
VAR _RATE = DIVIDE(_VALUE_1-_VALUE_2,_VALUE_2)
RETURN
_RATE

1 Week = 
VAR _DATE_1 = MAX('Sheet1'[Date])
VAR _DATE_2 = MAX('Sheet1'[Date])-7
VAR _VALUE_1 = CALCULATE(MAX('Sheet1'[Value]),'Sheet1'[Date]= _DATE_1)
VAR _VALUE_2 = CALCULATE(MAX('Sheet1'[Value]),'Sheet1'[Date]=_DATE_2)
VAR _RATE = DIVIDE(_VALUE_1-_VALUE_2,_VALUE_2)
RETURN
_RATE

1 Month = 
VAR _DATE_1 = MAX('Sheet1'[Date])
VAR _DATE_2 = EDATE(MAX('Sheet1'[Date]),-1)
VAR _VALUE_1 = CALCULATE(MAX('Sheet1'[Value]),'Sheet1'[Date]= _DATE_1)
VAR _VALUE_2 = CALCULATE(MAX('Sheet1'[Value]),'Sheet1'[Date]=_DATE_2)
VAR _RATE = DIVIDE(_VALUE_1-_VALUE_2,_VALUE_2)
RETURN
_RATE

...

3 Year = 
VAR _DATE_1 = MAX('Sheet1'[Date])
VAR _DATE_2 = EDATE(MAX('Sheet1'[Date]),-36)
VAR _VALUE_1 = CALCULATE(MAX('Sheet1'[Value]),'Sheet1'[Date]= _DATE_1)
VAR _VALUE_2 = CALCULATE(MAX('Sheet1'[Value]),'Sheet1'[Date]=_DATE_2)
VAR _RATE = DIVIDE(_VALUE_1-_VALUE_2,_VALUE_2)
RETURN
_RATE

3. result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Hi ahadtk ,

How about adding a condition to return the Friday date if it is a weekend? Like this:

1 Week = 
VAR _DATE_1 = MAX('Sheet1'[Date])
VAR _DATE_2 = MAX('Sheet1'[Date])-7
VAR _DETE_3 = SWITCH(TRUE(),WEEKDAY(_DATE_2,2)=6,_DATE_2-1,WEEKDAY(_DATE_2,2)=7,_DATE_2-2,_DATE_2)
VAR _VALUE_1 = CALCULATE(MAX('Sheet1'[Value]),'Sheet1'[Date]= _DATE_1)
VAR _VALUE_2 = CALCULATE(MAX('Sheet1'[Value]),'Sheet1'[Date]=_DETE_3)
VAR _RATE = DIVIDE(_VALUE_1-_VALUE_2,_VALUE_2)
RETURN
_RATE

Best Regards,
Gao
Community Support Team