Hello - Does anyone know the best way to alter my Pivot Table to give me TTM P&L data based on the last month actuals? Ideally, I would like this to update automatically as we move through time rather than me having to add all the month data together manually.
Hey Kaylen, Not sure what you are really after. But would be happy to chat with you about what you are tying to do..QQube has a lot of dating features so it should not be difficult geting last month's actuals.
Thanks, Fran - I am looking to have the sum of the last 12 months of data in the P&L by class. For this month, I would be looking for revenue and expense data summed for August 2019 through July 2020. I do not want to see the months in the columns, just the class, but I would like the numerical data to represent the summation over a period of 12 months. Does that help clarify?
I get it.
Is Aug- july your fiscal year ? Or do you just 12 months of data. Look towards the bottom of the Profit and Loss table fields (after all of the "period") ....One of the values is "Current Year to Last Period Actual".. This should give you 12 months if Aug - July is your fiscal year. Just drag field over to the "values" section of Pivot and the amounts will calculate correctly.
If that doesn't suit your needs, there are PowerPivot formulas which can be used to get the last 12 months of data summed.. but it would require a new formula for you.
Feel free to email me off line if you like...
Thanks for reaching out to me. But I wanted to end this dialogue (In case Others need the info also) by adding that if you are using QB Enterprise, you have access to the FreeStyle Financial Summary. This "analytic/report" has an actual calendar associated with it, allowing you to pick the dates to summarize. You still will have to generate a Dax measure, but its much easier with this "analytic/report".
Choose a location