Calculate growth over time based on User opt-in date and opt-out date

[Tvineyy]

Hi All,

I am looking to create a line chart to show the cumulative growth in some email audience data (600K + rows). In the table below there is an example of the data with opt-in date (when user joined the email list) and sometimes the opt-out date (If the value is NULL then they are still opted in).

How can I create a line chart that shows cumulative growth in email audience but takes into account when each user opted out?

I also need to keep the User ID column to join with fact tables.

User ID	Opt in Date	Out-out Date
101	01-01-2020	01-12-2020
102	02-01-2021	02-02-2021
103	03-01-2022	NULL

Thanks in advance!

[v-kkf-msft · Accepted solution]

Hi @Tvineyy ,

Please create a date table:

Then create the measure:

Measure = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[User ID] ),
    FILTER (
        'Table',
        'Table'[Opt in Date] <= MAX ( 'Calendar'[Date] )
            && (
                'Table'[Out-out Date] >= MIN ( 'Calendar'[Date] )
                    || ISBLANK ( 'Table'[Out-out Date] )
            )
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Tvineyy , check it it very similar to active employee

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...