Showing running number of active users (at each given date)

[Olivier960]

Good day to all,

I have a database of Users. Each user has a Start Date and a End Date. When a given date (for instance today) is between those 2 dates, it means the user is Active. The database looks like this:

UserId	StartDate	EndDate
1	01/01/2022	08/01/2022
2	04/01/2022	11/01/2022
3	07/01/2022	14/01/2022
4	10/01/2022	17/01/2022
5	13/01/2022	20/01/2022

I would like to create a line chart that shows how many users were active at each given date (since the first Start date in the entire database). So on the horizontal axis, I would have all the dates since the first Start date. And as Values, I would have the number of UserId for which the Start Date was earlier than that given date, and the End Date later than that given date.

Appreciate a lot all the support I can get, I've been struggling with this for a while.


 

[DataInsights · Accepted solution]

@Olivier960,

Try this measure. There is no relationship between the two tables, and DimDate[Date] is used as the X axis.

Active Users =
CALCULATE (
    COUNTROWS ( Table1 ),
    Table1[StartDate] < MAX ( DimDate[Date] ),
    Table1[EndDate] > MAX ( DimDate[Date] )
)

[Olivier960]

Thank you so much, you made my day!