We're utilizing custom analytical queries for a lot of our core financial reports because it gives us more flexibility than financial statement versions for things like P&L categorization. Typically we've done them in excel, but I'm trying to steer our team out of that model so they can go spend their time around close in analysis not manual report building.
One of the reports that I'm struggling to replicate on the surface sounds pretty common and simple: P&L comparing the last 12 periods of actuals. While I can get the report to show the periods as the columns and the custom categories of our P&L, I'm having a difficult time figuring out how to get the report to automatically filter to show only the last full fiscal period and the 11 preceding periods. Having the user select the 12 periods they want every time is a pain and I'm imagining there has to be a way to do this. The thing that is tripping me up is that the fiscal year and period fields are not date centric within the custom analytical query and we have 13 periods (4 weeks a piece which do not align with calendar months).
Does anyone have any tips or experience creating a query/report with a rolling automated filter?