How to set up a slicer that works with multiple tables?

Arham Nadeem 0 Reputation points

I have a dataset of sales. "Report number" column is the unique identifier while the date column has duplicates (as multiple orders are made in a day). there are other columns such as "salesman", "region", "category", "subcategory". I am in the process of creating a dashboard for the sales head where they can choose from any of the above named criteria (apart from report number) to filter the data to find the trend of sales under certain conditions.

since my dates have duplicates, it is difficult to set up a Month on month percent change.
I wanted some help regarding how to set up MoM, YoY, QoQ in a way that if someone selects a certain month from the slicer, they get MoM % change. if they select a certain year then they get the % change from the last year and if they select a certain quarter then they get the QoQ change.

Additionally, I also want to show last 6 months' data. so if someone selects a month from the slicer, they should be getting a trendline showing the sales for the past 6 months.

I have read that I should generate a new table with calendar dates and form a relation of that with my already created sales table (my sales table is basically an all in one table). however, with these relations set up, a slicer doesnt work on both table rather only on one of them. how do I go about this?

A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,529 questions
{count} votes