Well ok, here is my suggestion.
I am assuming you have 3 tables: Calendar, Fact and Users.
Calendar and Users are both linked to your Fact table through a '1 to Many' relationship, respectively with [DateKey] and [UserID].
Below the steps :
1. Compute the first date usage (or purchase) ever in the Users table through a calculated column:
DateOfFirstUsageEver = Calculate ( min (Fact[DateKey]) )
This calc. column basically uses context transition (so you need to wrap the min into a calculate!!)
2. Create a relationship between Calendar[Date] and Users[DateOfFirstUsageEver]. This relationship is inactive by default (because of ambiguity, e.g there would be several paths to reach Users Table from Calendar Table) but you can leverage it to compute your number of new Users.
NumOfUsers = Countrows(Users)
NumOfNewUsers = Calculate ( [NumOfUsers] , USERELATIONSHIP(Users[DateOfFirstUsageEver], Calendar[Date]))
USERELATIONSHIP locally activates the relationship (e.g only for this particular measure) between Users and Calendar we have just created and allows you to compute the number of new users in the current filter context (e.g takes into account the dates you select)
3. You need a measure to compute the number of distinct users in the current filter context.
TotalUsers = Distinctcount(Fact[UserID]) works fine.
Your users are either returning or new. As a consequence:
TotalUsers= NumOfNewUsers + NumOfReturningUsers (1)
From (1), we have: NumOfReturningUsers = TotalUsers- NumOfNewUsers
You now have both New and Returning Users measures. Play with it !
Note: I generally never use calculated columns. As the DAX gurus suggest, calculated columns should only be considered when slicing of filtering data (mainly because of the Vertipaq columnar storage... adding columns to your model requires more space!). But in this particular case, I find it very useful.
Hope it helps 🙂
Feel free to ask if you have any questions !
Tristan
Data & BI consultant at AZEO
