Hi Mynda,
In your excellent rolling 12mth slicer video ( https://www.youtube.com/watch?v=RhkL11U6zMc) you grabbed the 12 months preceding the TODAY() date. How do I instead calculate the rolling 12mth performance of each salesman, for each month in the pivot report, in other words a history of their 12mth performance instead of just today's snapshot?
Regards,
Peter Warburton
Hi Peter,
I'm not following, sorry. The chart shows the previous 12 months. To me this is a history of 12 month's performance, so I'm not understanding how what you want is different. Perhaps you could provide an example file.
Mynda
Hi Mynda,
I want each month column to contain the sum of the preceding 12mths, so January 2021 would contain the sum of Feb 2020 to MTD Jan 2021, February 2021 would contain sum of Mar 2020 to MTD Feb 2021.
The goal is to follow the rolling "trailing-year's-Performance" month by month, to see what "annual performance band" they were/are in at any given month. A smoothed performance history, if you like.
My understanding of your chart is that it is displaying the monthly total for each of the 12mths prior to today. In other words it forgets anything more than a year ago, so is a snapshot in time.
Hope this clarifies what I'm trying to achieve.
Regards,
Peter
Hi again,
Attached is the excel equivalent of what I am trying to achieve in a pivot table for rolling and trailing annual performance
Regards,
Peter
oops, looks like I was getting ahead of myself. I've just started Time intelligence functions chapter in PowerQuery and see the PREVIOUSYEAR function.
Is that what I should be using?
Peter
Solved it:
=calculate([Total Orders $],DATESBETWEEN(Dates[Date],LASTDATE(Dates[Date])-364,LASTDATE(Dates[Date])))
Peter
