Want to make it so that a Pivot will show how many days a vehicle is used monthly rather than total journeys

Question

So the scenario I am in in that my company has multiple vehicles which can be taken as part of a car pool. Some cars are more popular to be taken than others. At the moment, on a spreadsheet we manually record the journey number, date of journey, time taken (and returned) plus various other details about the journey etc.

I want to be able to track on how many days each month, each individual vehicle is used. At the moment my pivot table looks roughly like:

Count of Journey No	Column Labels
Row Labels	01-May-25	02-May-25	03-May-25	04-May-25	05-May-25	Grand Total
AB01 ABC	2	1	1	2	1	7
AB02 DEF	1			1		2
AB03 GHI		2	1			3
AB04 JKL	1	1	1	1	1	5

(First column is local vehicle registration format)

The numbers in the example are the number of journeys per day so for example vehicle AB01 ABC was taken out twice on the 1st May, once in the morning and once in the evening. I want it so that the Grand total for each vehicle would be 5, 2, 2 and 5 respectively. Ultimately I want the pivot to look something like:

Count of Journey No	Column Labels
Row Labels	January	February	March	April	May	Grand Total
AB01 ABC	31	21	24	22	5	103
AB02 DEF	11	8	5	10	2	36
AB03 GHI	7	12	10	9	2	40
AB04 JKL	31	28	25	20	5	109

(May has low numbers as we are assuming it is the beginning of May and not a full month)

At the moment as a workaround, I could add a column outside the pivot and use COUNT but upon tracking for the whole year, I'd have to set up a new pivot for each month and this would be messy.

Answer 1

Add your data to the Data Model. Then use Power Pivot to create the PT with a distinct count of dates.