cancel
Showing results for
Did you mean:
Frequent Visitor

## How to pull and summarize data across months.

I need to create a trend report that will pull the most recent data and summarize by month.  The data consist of Patient Scores.  A patient can have one or more scores in a given month, or they may have none.  If they have a score, then I want to pull the most recent score for that month.  If they do not have a score in a given month, then I want to pull the last score that they had.  Here is my sample data....

Patient                 Date                Score

Joe                     01/03/2018         7

Sara                   02/02/2018          7

Ryan                  02/05/2018          7

Sara                   02/28/2018          4

Ryan                  03/01/2018          1

Joe                    03/22/2018           6

Using the above data..... My trended report would show the following.....

Jan           7      (just Joe)

Feb          18    (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          11   (Sara's score from Feb of 4, plus Ryan's March score of 1 and Joe's March score of 6)

Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: How to pull and summarize data across months.

Hi @Geeco1

You need to build the model simple model:

The last column in the Calendar table is a calculated column such as this:

Has Score = NOT( ISEMPTY( RELATEDTABLE( Scores ) ) )

And you should be able to achieve this with this measure:

Measure =
IF (
CALCULATE ( MAX ( Scores[Date] )ALL ( Scores ) ) >= MIN ( 'Calendar'[Date] ),
SUMX (
VALUES ( Patients[Patient] ),
CALCULATE (
MAX ( Scores[Score] ),
LASTNONBLANK (
CALCULATETABLE (
VALUES ( 'Calendar'[Date] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ),
'Calendar'[Has Score] = TRUE ()
),
CALCULATE ( COUNTROWS ( Scores ) )
)
)
)
)

Proud to be a Datanaut!

2 REPLIES 2
Super User

## Re: How to pull and summarize data across months.

Hi @Geeco1

You need to build the model simple model:

The last column in the Calendar table is a calculated column such as this:

Has Score = NOT( ISEMPTY( RELATEDTABLE( Scores ) ) )

And you should be able to achieve this with this measure:

Measure =
IF (
CALCULATE ( MAX ( Scores[Date] )ALL ( Scores ) ) >= MIN ( 'Calendar'[Date] ),
SUMX (
VALUES ( Patients[Patient] ),
CALCULATE (
MAX ( Scores[Score] ),
LASTNONBLANK (
CALCULATETABLE (
VALUES ( 'Calendar'[Date] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ),
'Calendar'[Has Score] = TRUE ()
),
CALCULATE ( COUNTROWS ( Scores ) )
)
)
)
)