Getting frequency of Appointments over time as Montly, Weekly, Yearly etc

Welcome to the forum.

We need to start with a sample workbook - please see instructions in the yellow banner up top.

Hello,
Here is an example spreadsheet.
With a much bigger spread of dates (like 200,000 with a lot of different clinics), I would like to know for each clinic on average how often the different types of patients are coming in based on their diagnosis and their appt date.

OK - so what we need is for you to mock up (manually) the outcomes you wish to see for that sample dataset. Please do that and attach the updated workbook.

No, sorry - we need both source data and expected outcomes in one workbook.

Oh, dear!

In the outcomes you have diagnoses A and B - in the source data there is only diagnosis X.

What we need is sample data that shows what you wish to achieve. We can't see this if the sample source data has none of the summary data in it!

I am afraid I have run out of time for today - hopefully someone else will be able to take this further for you.

I apologize! I didn't know it had to be exactly.

That's what "realistic & representative sample data" means in the banner up top.

It's taken three hours, but finally we have something meaningful from you - thank you.

I'm in the UK - I have to go now and start preparing for our Sunday dinner! I am sure someone else will have some ideas for you.

Here's one approach, using PowerPivot.

First, I'd add a table to define your frequency categories - something like:

Capture.PNG

Format your Visits source data as a table.

Load both tables to the Data Model - I use Power Query (Get & Transform Data) for this.

Now create several DAX Measures in your data model:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Now you can create a pivot table from the data model, using Visits[Clinic] and Visits[Diagnosis] in rows, Categories[Category] in Columns, and [Patients Per Category] in Values.

Capture1.PNG

You can simply refresh all data, to update when your source data changes, or when you make changes to your Frequency Categories.

See attached workbook for worked example - I added a few additional rows to your source data, to demonstrate the calculations.