Forum Discussion

ahadtk's avatar
ahadtk
Frequent Visitor
3 years ago
Solved

Price Index Table Over the Time

Dear All!

We have a table like below:

Screenshot UP,

Link to the Excel file:

We have several products with their prices over the time since 01-01-2019 so far...

We need a table in Power Bi that show the price indexes on the indexes below:

  • 1 Day
  • 1 Week
  • 1 Month
  • 1 Quarter
  • 6 Months
  • 1 Year
  • 2 Years
  • 3 Years

e.g. Product 1 has 12% decrease its price in the past 24 hrs,

Product 3 has 44% increased in its price since 6 months ago,

 

Something like below:

 

Many thanks for the help,

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

  • Anonymous's avatar
    Anonymous
    3 years ago

    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

6 Replies