Count number of trainee improved compare to previous test score

[tienkien]

Hello,

I have a score database of the trainees after training activities like this:

Date	TraineeID	Score	CourseOder
1/25/2022	1	4	1
1/25/2022	2	5	1
1/25/2022	3	5	1
1/25/2022	4	5	1
1/25/2022	5	6	1
3/26/2022	1	4	2
3/26/2022	2	5	2
3/26/2022	3	7	2
3/26/2022	4	5	2
3/26/2022	5	4	2
5/15/2022	1	8	3
5/15/2022	2	10	3
5/15/2022	3	4	3
5/15/2022	4	9	3
5/15/2022	5	1	3
7/16/2022	1	4	4
7/16/2022	2	2	4
7/16/2022	3	3	4
7/16/2022	4	7	4
7/16/2022	5	9	4

I want to calculate how many trainees improved after training courses.

The improvement can be measured by any positive change between any two courses (data points) in a period:

Example:

for Q1-2022 (Jan to March), trainee number 1 wasn't improved  => not counted

for semi-annual (Jan to June), trainee number 1 was improved => counted

for the report from May to July, trainee number 1 was not improved => not counted

for the annual report (Jan to Dec), trainee number 1 was improved => counted

Thank you.

TK.

[v-shex-msft · Accepted solution]

HI @tienkien,

You can try to use the following measure formula to get the count of improved trainee:

formula =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            FILTER ( ALLSELECTED ( 'Table' ), [CourseOder] <= MAX ( 'Table'[CourseOder] ) ),
            [CourseOder],
            [TraineeID],
            [Score],
            "HistoryScore",
                MAXX (
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        [CourseOder] < EARLIER ( 'Table'[CourseOder] )
                            && [TraineeID] = EARLIER ( 'Table'[TraineeID] )
                    ),
                    [Score]
                )
        ),
        [HistoryScore] <> 0
            && [Score] > [HistoryScore]
    )
)

Regards,

Xiaoxin Sheng

[tienkien]

@v-shex-msft 

It works exactly what I want.

Thank you very much!

TK

[tienkien]

Does anyone have the solution to this problem?

Regards,

TK