Below is a very simple table, which logs whether you’ve worked out on a given day. The Streak column uses the Pack to calculate how long the streak was on each day.
Try it! Add rows and toggle checkboxes to see it recalculate the streaks.
There are no rows in this table
You’re on a
Keep it up!
Select the columns to used to calculate the streak.
The first must be a Date column, representing the date of the entry.
The second must be a Checkbox column, indicating if the goal was met on that date. You can use a formula to calculate the value of this checkbox column, if it is dependent of various data points in the row.
Add a new Number column to hold the streak value.
Add a formula to that column, with the following format:
Replace Table with the name of the table, Datewith the name of the Date column, and Checkboxwith the name of the checkbox column.
For example, here’s the formula used in the demo table above:
That’s it! You should now have a streak value for each row in your table.
How it works
Unlike most formulas which work on a single row at a time, calculating a streak requires looking at all of the previous rows. To improve performance, the formula calculates all of the streak values for the entire table in one go.
For example, here is the result of calling the Streaks() formula on the demo table above.
Streaks(Workouts.Date, Workouts.Complete) =>
Notice how you pass in the entire column of dates and the entire column of checkboxes, and then get back an entire column of streaks.
Once you have the total set of streaks, it’s just a matter of displaying the right value for each row. Table.Find(thisRow) gets the row number of the current row, and then .Nth() pulls out the corresponding streak at that index.
What happens if you get sick and miss a few days? Should that break the streak? Well, the answer may depend on how strict you want to be with yourself, but you can use the Streaks() formula either way!
To allow for gaps in the log without breaking the streak, set the advanced option allowGaps.