Forum Discussion
Count dates by calendar table
Hi All,
Using Excel 365,
I'm hoping to find some help to find a way to count multiple date fields by year and month of my calendar table. The result I'm getting when in create my pivot table is the a repeated total count of the date in the that field against each month. The result I'm looking for is a pivot table with 10 fields that i can add one date slicer that will show the number of activities completed in said year and month. (I've only shown 2 fields in the example not all 10)
I did manage to get this to work by creating 10 x queries with each query reducing the data set to just the 1 column i needed, adding these to the data model and then creating 10 individual relationships to the calendar look up table. Not exactly the most efficient way.
Tris_Beachwood Thanks! Very helpful.
You can't relate multiple Date columns in the activity table to the Date in the calendar table. I took your table as the source for a Power Query. Cleaned-up a bit and focussed only on the columns you seem to interested in. Then, the key step is to unpivot the table so that you are left with one single date column in the activity table. Then, the rest is a piece of cake.
See attached.
7 Replies
Tris_Beachwood Did you create a relationship between the Date in the Activity table and the Date in the Calendar table?
- Hi
Yes I did, but I can only get the relationship with 1 x field. All other relationship dates are inactive. So all fields are then filter by to active relationship.Tris_Beachwood Difficult to imagine how your data tables are structured/related. Did a mock-up schedule and could replicate the wrong type of answer, simply by not setting the relationship right.
Can you upload the file? Replace any confidential information with fake data.
