% change with random dates

[Anonymous]

I have some workout data that includes a number of fitness tests and I want to be able to calculate progress in these tests. The tests don't take place at regular intervals and there could be any number of them. 

The sample data and some code is below. I want the % change, in this case it would be (125-107)/107 = 16.8% but I'm getting -62.5% with the calculation below. From what I can work out the current FTP and previousDatewithFTP are calculating correctly, but the previous FTP isn't.

Any ideas what is going wrong?

Sample data:

Date	Title	Avg. Watts
02/05/2020	20 min FTP Test Ride	107
17/10/2020	20 min FTP Test Ride	125

Code:

%FTP change =
VAR currentFTP =
CALCULATE (
SUM ( workouts[Avg. Watts] ),
FILTER (
ALL ( workouts), SEARCH ( "FTP Test Ride", workouts[Title],, BLANK () ) > 0 && workouts[Date] = MAX ( workouts[Date] )
)
)
VAR previousDatewithFTP =
CALCULATE (
MAX ( workouts[Date] ),
FILTER (
ALL ( workouts ), SEARCH ( "FTP Test Ride", workouts[Title],, BLANK () ) > 0 && workouts[Date] < MAX ( workouts[Date] )
)
)
VAR previousFTP =
CALCULATE (
SUM ( workouts[Avg. Watts] ),
FILTER (
ALL ( workouts ),
workouts[Date] = previousDatewithFTP
)
)
RETURN
DIVIDE ( currentFTP - previousFTP, previousFTP, 0 )

[lbendlin]

try this version:

%FTP change =
VAR currentFTP =
CALCULATE (
SUM ( workouts[Avg. Watts] ),
FILTER (
ALL ( workouts), SEARCH ( "FTP Test Ride", workouts[Title],, BLANK () ) > 0 && workouts[Date] = MAX ( workouts[Date] )
)
)

VAR CD = MAX(workouts[Date])
VAR previousDatewithFTP =
CALCULATE (
MAX ( workouts[Date] ),
FILTER (
ALL ( workouts ), SEARCH ( "FTP Test Ride", workouts[Title],, BLANK () ) > 0 && workouts[Date] < CD
)
)
VAR previousFTP =
CALCULATE (
SUM ( workouts[Avg. Watts] ),
FILTER (
ALL ( workouts ),
workouts[Date] = previousDatewithFTP
)
)
RETURN
DIVIDE ( currentFTP - previousFTP, previousFTP, 0 )

[Anonymous · Accepted solution]

Thank you - I tried the above but still got the same issue. Managed to fix it in the end by making the filter for the previousFTP calculation the same as the others (adding the bit in red):

VAR previousFTP =
CALCULATE (
SUM ( workouts[Avg. Watts] ),
FILTER (
ALL ( workouts ), SEARCH ( "FTP Test Ride", workouts[Title],, BLANK () ) > 0 &&
workouts[Date] = previousDatewithFTP
)
)

Thank you for the advice!

[lbendlin]

What's the reason for using SEARCH ?  Do you have scenarios where you want to compare activities where the title is not exactly the same?  Please provide more context.

[Anonymous]

The wider dataset has many other activities with different titles, but it's only the ones with "FTP Test ride" in the title that I want to compare.