How to pull and summarize data across months.

Question

I need to create a trend report that will pull the most recent data and summarize by month.&nbsp; The data consist of Patient Scores.&nbsp; A patient can have one or more scores in a given month, or they may have none.&nbsp; If they have a score, then I want to pull the most recent score for that month.&nbsp; If they do not have a score in a given month, then I want to pull the last score that they had.&nbsp; Here is my sample data....&nbsp;Patient&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Score&nbsp;Joe&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/03/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7Sara&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;02/02/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7Ryan&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02/05/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7Sara&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;02/28/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4Ryan&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 03/01/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1Joe&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 03/22/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6&nbsp;Using the above data..... My trended report would show the following.....Jan&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7&nbsp; &nbsp; &nbsp; (just Joe)Feb&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 18&nbsp; &nbsp; (Sara's last score of 4 + Ryan's score of 7 + Joes score of 7 Jan since he didn't have a Feb entry)Mar&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11&nbsp; &nbsp;(Sara's score from Feb of 4, plus Ryan's March score of 1 and Joe's March score of 6)&nbsp;Any ideas?

liviolanzo · Accepted Answer

Hi&nbsp;Anonymous&nbsp;You need to build the model simple model:&nbsp;&nbsp;The last column in the Calendar table is a calculated column such as this:&nbsp;Has Score = NOT( ISEMPTY( RELATEDTABLE( Scores ) ) )&nbsp;&nbsp;And you should be able to achieve this with this measure:&nbsp;&nbsp;Measure&nbsp;=IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;MAX&nbsp;(&nbsp;Scores[Date]&nbsp;),&nbsp;ALL&nbsp;(&nbsp;Scores&nbsp;)&nbsp;)&nbsp;&gt;=&nbsp;MIN&nbsp;(&nbsp;'Calendar'[Date]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;SUMX&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES&nbsp;(&nbsp;Patients[Patient]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MAX&nbsp;(&nbsp;Scores[Score]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LASTNONBLANK&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATETABLE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES&nbsp;(&nbsp;'Calendar'[Date]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;ALL&nbsp;(&nbsp;'Calendar'&nbsp;),&nbsp;'Calendar'[Date]&nbsp;&lt;=&nbsp;MAX&nbsp;(&nbsp;'Calendar'[Date]&nbsp;)&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Calendar'[Has&nbsp;Score]&nbsp;=&nbsp;TRUE&nbsp;()&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;COUNTROWS&nbsp;(&nbsp;Scores&nbsp;)&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;&nbsp;

Forum Discussion

How to pull and summarize data across months.

2 Replies

Related content

Parameter in summarize

Don't Summarize....still summarizes...?

GroupBY and summarize

Merge Summarize Table

Month to Month Flow Calculation