Running Total of Distinct Values

If you have 5,000 distinct values or less in your dataset of interest, and your model has a MIN(date) measure, you can do this in the explore (otherwise you’ll need to use similar logic, but in the SQL of a derived table)

First, dimension by your entities / distinct values, measure them by their min (earliest) date, and sort by the min date. Using your example data, you’ll get a table like:

Then add two table calcs, one for a running total, and one to only show the last row of each Min Date value.

• 
  Running Count: row() + if(is_null(${min_date}),0,0)
  
  ^ We add the +if(...) to reference the measure and ensure this goes to the right of it, for visualization purposes


• 
  Show row - ${min_date} != offset(${min_date},1)
  
  ^ We’ll show only the last row (running count) within each value of min date

Finally, hide the first column, and use “Hide No’s” on the last column, and pick a line vis.

If you want to pre-calculate some of this in SQL and then allow users to pick their own timeframes, it will help to create a roll-up table with dimensions “date”, “prior date”, and measure “count”. Start by using a window function to partition by entity, order by date, and add a ‘lag’ function:

Then group and aggregate that to get this rollup table, which you would persist:

Then to get any cumulative distinct count, you can just group by date (or some function thereof, like week or month), filter on last date being outside of the range for which you want the measure to be distinct (because the ones where last date is within in that same range are not unique), and sum.