Rolling Months on Pivots and KPIs

04/14/21
Answered - Pending Review

I have a question on how to update my KPI's where the data populates by month and will automatically start copying from the new month while dropping out an old month that is 13 months back? This will always show me the latest 12 months. I have multiple sites that I am running the reports from and would like to automate this to remove the hands on work on a monthly basis.

Any advice would be helpful. We are using the pivot app to pull and sort the data, but having the issue with mapping the new month. The above dashboard you see is only one of about 100 that are being tracked.


Thank you

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Can you provide a screenshot of how your data is laid out that you are referencing for your charts?

    thinkspi.com

  • Lawrence KilduffLawrence Kilduff ✭✭✭✭


    Thanks Paul. They are sheets sometimes like this when you need to edit the metric back to capture that rolling 12 months.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Unfortunately your setup is going to require manual input each month because of the column names being the "Dates".


    I am unsure of exactly how many different data points you are collecting and what formulas you are using to collect that data, but if you were able to "flip" the layout so that your "Dates" were going down a column, you would be able to automate it using a TODAY function.

    thinkspi.com

  • Lawrence KilduffLawrence Kilduff ✭✭✭✭

    Paul


    thank you. I will try that with the team tomorrow and see if that will work. Greatly appreciated. Will let you know how it works out.


    larry

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️


    Feel free if you would like some pointers on how to automate the rolling portion. I have built out a handful of different "rolling" metrics for dashboards, so I should be able to at least point you in the right direction for those formulas.

    thinkspi.com

  • Diane MooreDiane Moore ✭✭✭✭

    @Paul Newcome, I just began using the pivot app and I watched a few videos. I am currently reluctant to use it too much because it is my understanding that when we roll into a new month, I must go in and rearrange, rename the new column. I am interested in what you shared above. If I swap the data round, does that prevent this ongoing maintenance issue?

    TIA!

    Diane

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Diane Moore That is correct. If you have the dates going down a single column and then separate columns for each data point, you should be able to use a formula to essentially automate the rolling time period with each month being on its own row and use formulas with cross sheet references that incorporate those dates to automatically display the correct amounts.

    thinkspi.com

Sign In or Register to comment.