topic Re: How to find the last value for each group in a reporting period and count the average for these rows in DAX Commands and Tips

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

marypal — Thu, 14 Sep 2023 10:17:01 GMT

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!

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

lbendlin — Sat, 16 Sep 2023 22:33:52 GMT

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.

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

Anonymous — Wed, 20 Sep 2023 09:50:15 GMT

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.

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

marypal — Wed, 27 Sep 2023 08:00:35 GMT

Thank you very much, Rico!