Previously we arranged our data to look at the max velocity reached over the last 12 days. This may give us an indication of maximum velocity reached recently but we also need an indication of how many exposures at or near max velocity an athlete has had. There is likely to be a quicker way to achieving this number but my solution has been to create a binary variable (1/0), where if an athlete exceeds 90% of their max velocity they get a 1, if not its a zero. Then we simply sum the binary column over a given 12 day time-span.
First we need to look at the binary column. This can be produced pretty easily with a simple “IF” formula in an empty column in power pivot
- Binary=IF(‘GPSData'[Pct Vmax] >= .9, 1, 0)
- IF “PCTVmax” is greater than or equal to .9, if true – 1, if false – 0
Now we can lean on the DATESINPERIOD function we used previously for the next step where we wrap it in a SUM function along with a CALCULATE function. We use CALCULATE along with SUM here as it allows us to use more than one filter in the formula.
- 12Day90%:=CALCULATE(SUM(‘GPSData'[Binary]), DATESINPERIOD(‘GPSData'[Date2],LASTDATE(‘GPSData'[Date2]),-12,Day))
We need a few steps here to allow this to show in our pivot table. Depending on how we want to show our data we have 2 main options, either:
- Date as a Filter and Athlete Name as a Row
OR
- Date as a Row and Athlete Name as a Filter
The first choice is more useful to show a number of athletes for a single day (such as a daily training report) whereas the second will show a single athlete over a number of days (such as an individual training load report or to track progress during rehab).
We have the option of including a column which gives extracts the date of the last greater than 90% velocity. Here we look at the most recent date where our Binary column is equal to 1.
- Last 90:=CALCULATE(MAX([Date]), FILTER(‘GPS Raw Data’, [Binary]=1))
Having looked at our options around velocity for a while now, next we will start to look at that magic number everyone likes to squabble about……the Acute Chronic Ratio and all its variations! If you have any questions about how we can look into speed further, feel free to leave a comment or contact me.
SportSciData 