+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    03-15-2018
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    27

    Question 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
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    12-06-2017
    Location
    Chicago, IL
    MS-Off Ver
    2016
    Posts
    28

    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!
    Last edited by chicagolarsons; 02-01-2019 at 11:19 AM.

  3. #3
    Registered User
    Join Date
    03-15-2018
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    27

    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. #4
    Registered User
    Join Date
    02-05-2019
    Location
    Pittsburgh, PA
    MS-Off Ver
    Office 365
    Posts
    2

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Move data, consolidate on one row for multiple clients
    By daisiedawn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-20-2018, 06:47 PM
  2. [SOLVED] Calculating Frequency of visits/usage at a car park
    By FredFitzgerald in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 11-09-2016, 02:45 AM
  3. Pivot table counts visits by month when one visit has multiple lines
    By BrookieOU in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 03-22-2016, 03:52 PM
  4. Multiple Pivot Tables with Data model - Source Problem
    By siva1612 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-16-2016, 12:27 AM
  5. Missing Dates for multiple clients
    By bcx in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-16-2013, 11:20 AM
  6. Replies: 1
    Last Post: 07-16-2013, 01:54 AM
  7. count / frequency of visits with conditions
    By maymano in forum Excel General
    Replies: 3
    Last Post: 08-19-2011, 03:32 AM

Tags for this Thread

Bookmarks

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