Most recent time value for specific ID and metric, formatted hh:mm:ss

[newuser1]

Hi!

Been struggling to get this to work...

I have a database of physical fitness tests done on random days over the years for a large number of people. For each person, I want to be return the most recent score for each test on a dashboard. Several of the tests are time based, such as a 3 mile run time, which is extra tricky in PBI. Ultimately, I want to be able to determine overall averages for each test, for each person, and compared to groups based on other variables/demographics, as well as show their most recent test score. Some tests for the same person were done on the same day and, of course, some people have blanks or zeros for scores, adding some complexity.

I've tried using group by, max, and other filters, creating measures and columns, but can't get it right. 

Example data below...

ID	GENDER	TEST DATE	3 MILE (mm:ss)	PUSHUPS (reps)
1	M	1/15/22		115
2	F	2/2/22	11:01
3	M	1/4/22	9:10
1	M	10/12/21	10:15
2	F	9/8/21	11:30
3	M	3/15/21	9:20
1	M	1/15/22	10:30
2	F	2/2/22		105
3	M	1/1/22		100
1	M	7/10/21		95
2	F	8/3/21		113
3	M	11/8/21		102

I'd like to have a column added for each test returning the most recent score and then be able to create measures determining the averages, formatted as appropriate.

Thank you!

[v-yiruan-msft · Accepted solution]

Hi @newuser1 ,

Since you have multiple testing columns, you can just select these testing columns as @lbendlin suggests and then unpivot these selected columns them:

Unpivot the selected columns

Then create the measures as below to get the recent score and the average score for per ID:

Recent score = 
VAR _selid =
    SELECTEDVALUE ( 'Table'[ID] )
VAR _selttype =
    SELECTEDVALUE ( 'Table'[Testing Type] )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[TEST DATE] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[ID] = _selid
                && 'Table'[Testing Type] = _selttype
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Score] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[ID] = _selid
                && 'Table'[Testing Type] = _selttype
                && 'Table'[TEST DATE] = _maxdate
        )
    )

Average score = 
VAR _seltype =
    SELECTEDVALUE ( 'Table'[Testing Type] )
VAR avgtimeinminutes =
    IF (
        _seltype = "3 MILE (mm:ss)",
        AVERAGEX (
            'Table',
            VAR timeinminutes =
                HOUR ( [Score] ) * 60
                    + MINUTE ( [Score] )
            VAR adjminutes =
                IF ( timeinminutes < 720, timeinminutes + 1440, timeinminutes )
            RETURN
                adjminutes
        ),
        AVERAGEX ( 'Table', VALUE ( 'Table'[Score] ) )
    )
VAR avgtimecorrection =
    IF ( avgtimeinminutes < 1440, avgtimeinminutes, avgtimeinminutes - 1440 )
VAR avghour =
    QUOTIENT ( avgtimecorrection, 60 )
VAR avgmin =
    MOD ( avgtimecorrection, 60 )
RETURN
    IF (
        ISINSCOPE ( 'Table'[ID] ),
        [Recent score],
        IF (
            _seltype = "3 MILE (mm:ss)",
            FORMAT ( TIME ( avghour, avgmin, 0 ), "hh:mm:ss AM/PM" ),
            avgtimeinminutes
        )
    )

Best Regards

[newuser1]

Thanks so much, this is exceptional!

[lbendlin]

First thing would be to unpivot and normalize your data.  You want to arrive at a star schema structure like 

Facts:

ID   Date  Activity Type  Value  

Then have another table for users with

Users:

ID  Gender

another table for activities

Activities:

Activity Type   Unit of Measure.

Calendar:

Date   Month  Year  etc.

and so on.  Once that is in place the visualizations will be easier.