I am trying to make a sales dashboard/report. I am pulling data from workbooks within a specific folder. I have combined them into one table in Power Query but I am stuck on how to best display month & year comparisons for the data.
I can do it on a table with an Index Match formula but it's not part of the data set or a pivot table. If I add a column to the pivot table showing the difference to the prev month/year it only works when they are not filtered out.
The dashboard/report needs to only show the selected month (via a slicer currently) as we have a lot of artists to list.
Example data:
I can do it on a table with an Index Match formula but it's not part of the data set or a pivot table. If I add a column to the pivot table showing the difference to the prev month/year it only works when they are not filtered out.
The dashboard/report needs to only show the selected month (via a slicer currently) as we have a lot of artists to list.
Example data:
| Artist | Year | Month | Sales |
| John Smith | 2019 | Sep | £34,656.41 |
| John Smith | 2019 | Oct | £1,329.16 |
| John Smith | 2019 | Nov | £36,623.60 |
| John Smith | 2020 | Sep | £25,300.52 |
| John Smith | 2020 | Oct | £51,799.10 |
| John Smith | 2020 | Nov | £4,070.83 |
| Anne | 2020 | Sep | £11,710.85 |
| Anne | 2020 | Oct | £9,409.09 |
| Anne | 2020 | Nov | £6,333.35 |
| Jane Doe | 2019 | Sep | £43,231.26 |
| Jane Doe | 2019 | Oct | £58,377.55 |
| Jane Doe | 2019 | Nov | £175,488.00 |
| Jane Doe | 2020 | Sep | £72,141.98 |
| Jane Doe | 2020 | Oct | £113,679.99 |
| Jane Doe | 2020 | Nov | £182,891.55 |
