This is not a question.
I’ve been using Quantrix for years, and I consider myself an advanced novice – and I’m even proud of that designation.
But I’ve been trying to figure out a simple way to create a trailing-12 P&L presentation while using a timeline in a model. For you experts, this is like falling off of a log. But if you are in my boat, it’s not super-easy. But I decided to heed the advice of Rich and Stephen – create a simple time utility matrix – but it’s a utility that serves just this one purpose – allowing a financial statement reader to see a trailing 12 view of the numbers. See my results below where actuals run through August 31, 2020:
Sorry for the image – my model is too large.
Notice the category header and the first item in the row – that’s the key to pulling this off. That column category and the Date ‘item’ reference the date utility I created below:
The date utility takes advantage of the following functions: edate, date, year, and month – nothing difficult. Notice the current date item above is redundant and not needed – I just wanted it visible at all times while creating the formulae (that item references an input matrix for the current time period).
With respect to the trailing 12 presentation, pulling the numbers for each P&L classification did not require a complex formula based on the way my detailed actuals are stored in the model. I did use a ‘using as’ for month and year to get the right numbers in the summarized version you see above.
My solution is probably a 2 on a 10 scale for elegance. So why am I showing it? Because Rich’s videos and this forum is my first point of reference when trying to figure something out. The concept above is not found in this forum, so it was time for me to give back. But I will yield if there’s a better way.
Similar to SAU’s great use of a filter, on the home page of the financials, I also employed a date filter that drives the trailing-12 month P&L.
If you want to include these views in your reporting, Rich and Sau (and Stephen too) are experts. Don’t be afraid to ping them with questions here in the forum.