Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ahadtk
Frequent Visitor

Price Index Table Over the Time

Dear All!

We have a table like below:

ahadtk_0-1668756527956.png

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:

ahadtk_2-1668762571121.png

 

Many thanks for the help,

2 ACCEPTED SOLUTIONS
v-cgao-msft
Community Support
Community Support

Hi @ahadtk ,

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

vcgaomsft_0-1669011281733.png

 

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:

vcgaomsft_1-1669011422690.png

 

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

View solution in original post

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

View solution in original post

6 REPLIES 6
ahadtk
Frequent Visitor

Hi @v-cgao-msft 

We have an issue here,

We have collected the only business days price over the time,

Meaning, we do not have the prices for Saturdays and Sundays,

So, if the last day or last month or ... would be a weekend day,

The new measures do not return any value 😞

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

Hi @v-cgao-msft 

Much appreciated 🙂

That's it!

ahadtk
Frequent Visitor

Hi @v-cgao-msft 

Many thanks for the help 🙂

v-cgao-msft
Community Support
Community Support

Hi @ahadtk ,

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

vcgaomsft_0-1669011281733.png

 

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:

vcgaomsft_1-1669011422690.png

 

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

amitchandak
Super User
Super User

@ahadtk , You have to create those many change % measures

 

some examples are here

 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

 

Others here

 

https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.