Totaling Months and Years from a Date Range

Hi folks,

I was hoping for some help. I am tallying raw data up on one tab and using a 2nd tab to calculate the occurrences of each month of each year (I've attached a file with mock data and desired outcomes). I ultimately want to create a pivot table/chart showing the average number of occurrences from month to month of each year and for each month from one year to the next (i.e. compare January 2015 to January of 2016, 2017 etc.). Any chance someone's either run into this type of situation before or can bail me out?

I did something kind of quickly....check out attachment. See if it helps or gives you ideas. GIVE Credit if I helped you ( use ADD REPUTATION star). thx

I put YEAR & Month into more manageable set up form me at least...then you can: 1. Get Counts by month & 2. compare YEARS with Months. It's easier to work with data in similar setups, then the Pivot table does the work! Cheers!

Here is Pivot Table with Average

That's awesome and it works well, relative to what I want to achieve - I really appreciate it. I'm trying to understand the formula but can't seem to wrap my head around it. I need to evolve the report to include other data, so I would love to better understand the "CUBE" functions as I have never used them before.

I think easier will be with the file from post #4

I think you may be right. Could you please tell me how you formulated the fx measure 1 field in order to establish the average? I need to replicate it but I don't know how it was done

Click on PT then go to the ribbon and select PowerPivot, then Measures, Manage Measures, Edit and you will see formula: =[Count of DATE]/[Distinct Count of DATE]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

I'm running Excel 2016 and my understanding is that PowerPivot is already built in (I don't see the option for it anywhere). Am I doing something wrong? The =[Count of DATE]/[Distinct Count of DATE] formula works really well for what I'm trying to achieve, but I can't seem to replicate it.

My COM Add-ins only contain 2 options: Acroboat PDFMaker Office COM Addin and Microsoft Power Map for Excel. I have nothing else in my "Calculations" option. The screenshot you sent me is exactly what I'm looking for.
Also, could you please tell me how to mark this thread as solved? I don't see anywhere on my screen