Forum Discussion
Calculate month to month movement in power query
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
4 Replies
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.
- Thanks for the direction.
Could you expand on what you would do / I need to look for in power pivot?
Thanks
