Hi,
I'm new to power pivots and I'm struggling to form a connection between tables based on date.
My main table is a budget spreadsheet, and each column is a month (e.g. oct21,nov21) with the relevant expenses to the row that are incurred in that month below it.
I have various other data sets I have made connections with and that has all worked fine but in wanting to analyse the data I want to be able to view costs by month, quarter, year etc.
So to do this I started making a separate table where the same months are listed below the header date, with other columns to correspond month, year etc. and thought I could connect this table too to form a common connection and fix the problem. However, I can't link the 'date' header with my main table where each header is a month because the month is in itself a header.
In a way I want excel to know that all those column headers can all be lumped together under the overarching header of 'date' if you see what I mean, but you can't have two headers for one column....
I have attached an example (not the real sheet) of what I have started in 2 tabs but I don't know if I'm going about it all the wrong way. All I want to be able to do in my powerpivot is view the monthly expenses in terms of month or quarter or financial year, calendar year etc.
Any help would be greatly appreciated.
Bookmarks