It is the data structure (a common name for it is an "Accumulating Snapshot")
that is not suitable for the task. The problem you want to solve requires the data
to be re-shaped into another structure (a common name is a "Periodic Snapshot").
This data structure would consist of distinct rows
for every Username and Date combination
where Date >= Creation Date AND Date < Deleted Date.
There are two common ways of re-shaping data
from an accumulating snapshot to a periodic one.
The former is by using a range-Join with a Calendar dimension
(which could be just a single Date column table).
Then simply counting Users per Dates would get the answer.
The latter is by making a so-called Transaction table
(the third main type of a fact table mentioned here)
by splitting every row into two (User-Creation / User-Deleted).
Then a RUNNING window calculation could make a final result.
I've taken both routes using a Sample Superstore as a datasource.
It has characteristics of an Accumulating Snapshot, namely,
Order Date and Ship Date for each Order ID.
So counting Orders to Ship would be very similar to your question.
The first route (Join with a Calendar dimension) is going via Cross-Join & Filter,
since there is no Non-equi Join option for Excel / Text datasources.
I'm using a common technique with the Link ID field (to join on 1 = 1).
The second route taken is going via Pivot option (for Excel / Text DS).
Please find the attached.
Hope it could help.
dim_Date_reshape_YF.twbx 2.8 MB