Using excel 2019. I am new to using pivot tables and power pivot. I've been trying to research how to accomplish what I want, but I am not getting everything to work properly. I created a sample data set and report and attached to help.
I have 2 data sources at the moment. Simplifying the info, data source 1 contains amount of product 1 sent out from Location 1 and Location 2, including costs and other data. Data source 2 is the same except it is for product 2. And to note, the dates of sending out product are not consistent between the two. It varies from once a week to once a month or anywhere in between. Also, there are often multiple entries for the same date (xx amount to customer 1, xx amount to customer 2).
What I am looking to do is create a simple report in which I create several pivot tables/charts from this data. Ideally, I would like to be able to open the excel file and have a dashboard where you can select which Location and what month report. So if I was generating a report for August 2019, the tables and charts throughout the report would have data displayed from August 2018 to August 2019.
What I have working so far is I've linked the source data into the data model, so one tab for each. I've gotten the Location to work but creating a table for my dashboard with the two locations and adding that to the data model, and then created a relationship between the locations table and data source 1 and then a second relationship between the locations table and data source 2. This allowed me to create a slicer to select which location on my dashboard.
What I'm struggling with is how to get the date range to work. Worst case I was thinking I could create a timeline for each data source and have both on the dashboard. But is there a better way to do this? Better than that I would like to have 1 timeline instead of 2 but since all of the dates are different, I don't understand how to link the timeline across data sources. Best, I would like the user to be able to select the year and month from dropdowns in other cells (or type it in I suppose), and have the report automatically pull the correct amount of historical data (so select 2019 from one dropdown and August from a second).
Sorry for the lengthy post but I'd appreciate any help I can get.
Bookmarks