Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Geeco1
Helper I
Helper I

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
LivioLanzo
Solution Sage
Solution Sage

Hi @Geeco1

 

You need to build the model simple model:

 

2018-11-20_8-54-38.jpg

 

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

 

2018-11-20_8-55-30.jpg

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

2 REPLIES 2
LivioLanzo
Solution Sage
Solution Sage

Hi @Geeco1

 

You need to build the model simple model:

 

2018-11-20_8-54-38.jpg

 

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

 

2018-11-20_8-55-30.jpg

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

That works... Thanks,

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.