Jul 24 2021 05:53 PM
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.
Jul 25 2021 12:36 AM
@Tris_Beachwood Did you create a relationship between the Date in the Activity table and the Date in the Calendar table?
Jul 25 2021 02:53 AM
Jul 25 2021 03:07 AM
@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.
Jul 25 2021 04:03 AM
@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.
Jul 25 2021 04:24 AM
Jul 25 2021 04:30 AM
Solution@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.
Jul 25 2021 05:02 AM
Jul 25 2021 04:30 AM
Solution@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.