Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

% change with random dates

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:

DateTitleAvg. Watts
02/05/202020 min FTP Test Ride107
17/10/202020 min FTP Test Ride125

 

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 )

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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!

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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. 

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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!