Forum Discussion
Solved
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 ...
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.
Riny_van_Eekelen see attached.
When i need my calendar table to provide the count of the fields completed in the filtered year & month. When i create the relationship as per below I just get the count of the fields completed in the same month as the active relationship.
I can achieve the outcome i want by creating lots of small data sets and then connecting them to the calendar table, i was just hoping to avoid doing this as its messy .