# Data Model Pivot to calculate frequency of visits for multiple dates and multiple clients

1. ## Data Model Pivot to calculate frequency of visits for multiple dates and multiple clients

Hi Everyone,

I have attached a sample worksheet which shows two separate tables with a relationship and the resulting pivot table. I am specifically looking at dosage of both frequency of visits and length of visits. For length, I was able to calculate it in the pivot table easily and compare with between actual and recommended. For frequency, I am having a harder time understanding how to generate a column which subtracts each date from the previous one to show number of days between visits and the average # of days for each client.

I need to be able to analyze the data together so it would be great if I could have it within the pivot table rather than having to create a work around column. Any help would be appreciated!

L

2. ## Re: Data Model Pivot to calculate frequency of visits for multiple dates and multiple clie

Hi,

I think I can help with this. The great thing about PowerPivot is you can create measures that can calculate these types of things on the fly. In your data model, first you need to link the two tables like this:

Capture.JPG

If you don't link them, the data in the measures will not calculate correctly.

Now to calculate the average days between visits, we need to know total days between the first visit and the last visit,and total number of visits. You create these measures below the 'encounters' table in your data model - just click in a cell below the table and type in the calculation.
Capture2.JPG
Here is how to create those measures in your data model:

Count days:=DATEDIFF(MIN(Encounters[Date of Service]),max(Encounters[Date of Service]),day)

Count Visits:=count(Encounters[Date of Service])

Average:=calculate([Count days]/[Count Visits])

When you go back to your pivot tables, you should now see these as selections in your available fields.
Capture3.JPG

Capture4.JPG

Hope that helps!

3. ## Re: Data Model Pivot to calculate frequency of visits for multiple dates and multiple clie

Hi Chicagolarsons,

Thank you so much for your response. I'm working on it now and I'm having trouble with the DATEDIFF formula. I think it might be because I'm using Excel 2013 and it wasn't introduced until 2016. Is there an alternative formula I could use? This is absolutely what I'm looking for-I can't thank you enough!

L

4. ## Re: Data Model Pivot to calculate frequency of visits for multiple dates and multiple clie

Lou, DATEDIFF is not a standard Excel function but a DAX function. See the file I recently e-mailed to you.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1