SOLVED

Count dates by calendar table

Copper Contributor

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)

 

Tris_Beachwood_0-1627171974126.png

 

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_1-1627173920079.png

 

 

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.

@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.

 

Tris_Beachwood_0-1627210864735.png

 

Tris_Beachwood_1-1627210967347.png

 

 

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 .






best response confirmed by allyreckerman (Microsoft)
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.

 

@Riny_van_Eekelen - I have so much to learn! Thanks heaps legend :)

I should have posted this question weeks ago!! lol
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
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.

 

View solution in original post