Dashboard/pivot tables with selected month compared to previous year?

I'm self-learning Excel with some misguidance from Copilot, so I might be making this more complicated than it needs to be. I'm a rookie (3 months experience now...) data analyst for a regulatory agency at a hotel casino resort. I always find examples for financial dashboards, but I deal in incidents. My dashboards have pivot tables to compare Investigators with counts of different incident types, monthly report volumes, etc.

Most departments in the agency I work for have multiple types of reports so for each department's dashboard I'm typically running two main data tables and feeding them into the data model with relational tables. The basic data table structure is columns for report number, report type, Investigator, date/time, etc., as well as some custom columns for month, assigned shift, shift incident occurred, etc., with each row a unique incident or log entry. I'm also using power query to automate the update process by dropping a raw data export from our logging software into a Teams folder. I finally got all that working great.

This is where I'm lost. I have a rolling 365-day filter in my merged power query exports, so when you're looking at incident counts in pivot tables you always know it's data based on the last 365 days. I'd also like to have a few charts where I could compare a given month to the same month the previous year. For instance, select Investigator: Chuck and Report Type: Arrest in some slicers and have the pivot table/chart show me monthly volumes for 2025 compared to 2026.

Copilot's suggestion was to maintain a rolling 730-day filter instead of 365, which made sense, but I want most of the pivot tables to only show 365 days of data, and then it started to get a bit into the weeds talking about someone called DAX and I don't remember what else to be able to compare months year to year. Is there already a clean way of doing this or am I asking for it trying to do this in Excel? I'm also concerned about file bloat slowing everything down to a crawl, my Surveillance dashboard is about 20MB with around 85,000 rows of data for log entries and 4500 rows for incident reports per year. I'd appreciate any suggestions.