This query is right up my street, I'm a former MicroStrategy developer/architect, PowerBI/Qlik/Salesforce/Tableu third line support and an ETL programmer.
Power BI used to not detect dates and hierarchy them into year, month quarter, date etc.
Back in the day most people would keep a good advanced query to generate a big comprehensive date table, then relationship the source into it and use the date table for all time slicers.
Excel and Power BI are pretty much the same in the query editor and relationships so the approach works in excel too.
Grab an old date table script from the net, one where you define an earliest date and final and it populates all possible dates.
This date list would also have columns to define the date in all possibilities like month name, month no, day name, day no, year, year-month, quarter.
Etc. Relate your source table into this and then use the date table as a slicer.
Edit:
This page looks decent
https://blog.enterprisedna.co/how-to-create-a-detailed-date-table-in-power-bi-fast/
I'm unsure of your MAT and QTR requirements and what this means but if you can work them into a date table column using a case/conditional statement then jobs good.
If this is some kind of filtered start-end accumulative total count then it's complex but could involve something like a dax column insert with some nice formula that will take ages to work out.
You're reinventing the wheel a bit though. You're using a federated enterprise grade reporting platform (MicroStrategy) that has data control and top tier ETL capabilities then exporting it to excel to use as an interactive report.
Excel is good for small report data sets when you need to power query for some simple ETL without comitting to a BI platform but you should use the excel data in a BI platform for user interactivity.
This will give nice slicers, aesthetics and lock them out from breaking things.
Second edit.
Read the question again.
You're using visual basic in excel to transform the data?
I once did that and I'd recommend against it, when you get promoted you can be hounded a bit to fix anything overly proprietary.
Power query is all standardised functions whereas VBA can be proper coding.
MAT
If the 12 months is rolling (ie 12 months from current month) then get the current date and evaluate each row's date and apply values in a new conditonal column.
QTR
Same type of thing, define the months in Quarter if different to standard financial definitions then create a column.
There's many ways to skin a cat, do this in vba or transform it in the query editor (
DateTime.LocalNow)
You could also do without a date table as someone mentioned above and do this all in one flat de-normalised table. I'd still recommend a date table though as its a good practice to denormalise things that are absolute like dates and may apply if you ever need to integrate a second data export for a different thing in a dashboard.
If you're looking for a neat visually appealing and interactive plaything for showing off data then try Power BI. It's essentially excel mixed with powerpoint and you can export a multi dashboard page PDF with mixed reports and narrative elements. Use power query to do the data transformations, even though Power BI can run everything verbatim from the power query it's good to keep this separate otherwise you need to change multiple reports every time you modify the data structure.
