Calculate month to month movement in power query

[geoffw100]

Hello, this is my first post!

I am working with a data file that provides sales data

It is presented as client name, sales Y2D 2021 and sales for the same period 2020

The file is sent each month

The monthly files are saved to a sharepoint

I can work out through power query how to pull the new file each month to calculate the growth each month on a Y2D basis

I would like however to be able to show month to month what the movement is

This would be through adding a new column each month with the year to date position that could then be graphed.

Is this possible without VBA.

I have very limited excel knowledge

[#2]

Hi @geoffw100 

Look into Power Pivot especially in the area of Data Modeling.  Therein your will find your treasure trove of answers.

[#3]

Thanks for the direction.

Could you expand on what you would do / I need to look for in power pivot?

Thanks

[#4]

Hi @geoffw100 

Here's a video about Power Query, Power Pivot, and Calendar Tables rolled into one video that covers same period last year type of implementation.

https://youtu.be/e-CFYi52gpc

[#5]

@geoffw100 

Question is bit abstract for the concrete suggestion.

I assume you have monthly totals for each client, not all transactions. Since all files are on Sharepoint folder I assume they all data from this files is collected and combine with Power Query in one table in resulting file which looks like

We would like to add M2M on client basis, not totals for all clients. To generate such transformation for one client

reference above query as new,

filter on one (any one) client,

sort by months,

add two Index columns one starts from 0 and another starts from 1.

merge table with itself on these two indexes

expand sales from resulting table (actually that will be ones for previous month)

add columns with different

remove unused columns

Preparing such query for one client transform it into the function with table as parameter. Group initial query by clients and apply above function as aggregation.

Result is like

My assumptions could be wrong, plus not clear how you would like to use results. Depends on this that could be another transformation and do you need to work with data model or not.