How to pull and summarize data across months.

[Geeco1]

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?

[LivioLanzo · Accepted solution]

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 ) )
            )
        )
    )
)

[Geeco1]

That works... Thanks,