Analyzing Day-Over-Day Changes With Power Query

Dany Hoter shows how to use Power Query to join one row to the next in a data set (given similar criteria) and do day-by-day comparisons:

I want to analyze the daily prices of certain commodities and be able to show the patterns of daily changes side by side. I want to calculate predictably the differences between each row and the row before. Each row represents data for a day, so the difference between rows is the daily change or in some cases, several days change.

I downloaded from Quandl 50 years of daily prices of gold and silver, and my goal is to calculate the daily changes in terms of dollars and percentage from day to day. Not all days are represented, so in case of a gap I calculate the number of days in the gap, and I divide the growth % by the number of days. I already imported and appended the data for both metals into a single table in Excel and we’ll start the process from this table.

Read on for the solution.  I’d just as soon LAG() the data in SQL Server, but if that’s not an option, this certainly works.

Related Posts

Embedding Images In Power BI

Zach Conroe shows how you can embed an image in Power BI: The good news is that there are workarounds to this challenge. We are going to reconstruct the above use case and demonstrate how to pull in images from a local database, and then use custom columns in Power Query to reformat the source […]

Read More

Getting A Specific Rank In DAX

Marco Russo shows us how to get the Nth element in a list using DAX: The complexity of the calculation is in the Nth-Product Name Single and Nth-Product Sales Amount Single measures. These two measures are identical. The only difference is the RETURN statement in the last line, which chooses the return value between the […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

October 2018
MTWTFSS
« Sep  
1234567
891011121314
15161718192021
22232425262728
293031