How to find the last value for each group in a reporting period and count the average for these rows

[marypal]

Hello,

Could anybody help me with my task, please.

I am using a DirectQuery mode.
My Table 1 contains columns:
- activity_type

- client_id

- activity_id

- activity_date

- score
I have a reporting date, the last date of a month, that we choose using a slicer. 
I need to find for each client_id, activity_type the last activity_id and corresponding score such that activity_date<=reporting_date and display that in Table 2.
After that we need to count for each activity_type the number of clients for which score is not null and find the average score.
Would be greatful for any hints

Many thanks!

[Anonymous · Accepted solution]

Hi @marypal ,

Please try code as below to create measures.

Avg Score = 
VAR _ReportDate =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR _GENERATE =
    GENERATE (
        VALUES ( 'Table 1'[activity_type] ),
        VALUES ( 'Table 1'[client_id] )
    )
VAR _ADD1 =
    ADDCOLUMNS (
        _GENERATE,
        "Score",
            VAR _MAXDATE =
                CALCULATE (
                    MAX ( 'Table 1'[activity_date] ),
                    FILTER (
                        'Table 1',
                        'Table 1'[activity_type] = EARLIER ( [activity_type] )
                            && 'Table 1'[client_id] = EARLIER ( [client_id] )
                            && 'Table 1'[activity_date] <= _ReportDate
                    )
                )
            RETURN
                CALCULATE (
                    SUM ( 'Table 1'[score] ),
                    FILTER (
                        'Table 1',
                        'Table 1'[activity_type] = EARLIER ( [activity_type] )
                            && 'Table 1'[client_id] = EARLIER ( [client_id] )
                            && 'Table 1'[activity_date] = _MAXDATE
                    )
                )
    )
RETURN
    AVERAGEX (
        FILTER ( _ADD1, [activity_type] = MAX ( 'Table 1'[activity_type] ) ),
        [Score]
    )

Number of Clients = 
VAR _ReportDate =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR _GENERATE =
    GENERATE (
        VALUES ( 'Table 1'[activity_type] ),
        VALUES ( 'Table 1'[client_id] )
    )
VAR _ADD1 =
    ADDCOLUMNS (
        _GENERATE,
        "Score",
            VAR _MAXDATE =
                CALCULATE (
                    MAX ( 'Table 1'[activity_date] ),
                    FILTER (
                        'Table 1',
                        'Table 1'[activity_type] = EARLIER ( [activity_type] )
                            && 'Table 1'[client_id] = EARLIER ( [client_id] )
                            && 'Table 1'[activity_date] <= _ReportDate
                    )
                )
            RETURN
                CALCULATE (
                    SUM ( 'Table 1'[score] ),
                    FILTER (
                        'Table 1',
                        'Table 1'[activity_type] = EARLIER ( [activity_type] )
                            && 'Table 1'[client_id] = EARLIER ( [client_id] )
                            && 'Table 1'[activity_date] = _MAXDATE
                    )
                )
    )
RETURN
COUNTX(FILTER(_ADD1,[activity_type] = MAX('Table 1'[activity_type]) && [Score]<>BLANK()),[activity_type])

Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marypal]

Thank you very much, Rico!

[lbendlin]

I need to find for each client_id, activity_type the last activity_id and corresponding score

The max([activity _id]) is not a problem.  However the "corresponding score"  is, as you cannot easily aggregate that.

You will likely need to use table variables  in your measure and filter that table variable.  Not sure how feasible that is in Direct Query mode.