get leaving and new subscriptions per day

[Spekko]

Hi all,

I have this (simplified) datamodel:

ID	Opt_In	Date
001	True	01-01-2021
001	True	02-01-2021
001	False	03-01-2021
002	True	02-01-2021
002	False	03-01-2021
003	True	04-01-2021

Some remarks:

• This dataset is expanded daily. Every morning, a list of IDs is loaded with their Opt_In state and the date as load date.
• The Opt_In may change from True to False and back to True (we're talking about newsletter subscriptions, where someone can subscribe, unsubscribe and subscribe again.
• The first entry of a new ID can be on any date, depending on the date that the ID subscribed. Example: 002 subscribed on 02-01-2021, unsubscribed a day later and subscribed again on 04-01.

What I want to show is the daily variation. So not only the total amount of subscribers (Opt_In = True) per day, but also the variance based on the number of new subscribers on a day and the number of unsubscribers on a day.

And this on a day per day basis 🙂

So more or less:

Date	Total subscribers	New subscribers	Leaving subscribers
30-06-2021	5000	5	3
31-06-2021	5010	15	5

where the total subscribers of 31-06 is the total subscribers of 30-06 + new on the 31st and leaving on the 31st.

How can I achieve that? Any help is very much appreciated!

[Spekko]

Hi all,

After some struggles, I managed to solve this with another approach. With this formula, I can mark the first time a line has been added:

Opt-In FA = if('Table'[opt_in_newsletter]="true",'Table'[scrape_time] = 
	CALCULATE(
		MIN('Table'[scrape_time]), 
		FILTER(ALL('Table'), 'Table'[email]=EARLIER('Table'[email]))
	),false)

 Based on that and variants for opt-outs I can count the number of new subscriptions and leaving subscriptions per day.

Thank you for your patience.

[v-stephen-msft]

Hi @Spekko ,

Can you expand your sample data and show the corresponding expected results based on your sample data?

The text description makes me a little confused.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Spekko , With sample data I am not able to relate the result

Try measures like
new till date = calculate(distinctCOUNT(Table[ID]), filter(all(Table[Date]), Table[Date] <= Max(Table[Date]) && Table[Opt_In] = true())) 
out till date= calculate(distinctCOUNT(Table[ID]), filter(all(Table[Date]), Table[Date] <= Max(Table[Date]) && Table[Opt_In] = false()))

remaining till date = [new till date] -[Out till date ]

[Spekko]

@amitchandak thank you for taking the time to help. I tried this with my available data-fields, leading to this formula:

New-Subscriptions = calculate(distinctCOUNT(Fact_newsletter_contacts[email]), filter(all(Fact_newsletter_contacts[Datekey]), Fact_newsletter_contacts[Datekey] <= Max(Fact_newsletter_contacts[Datekey]) && Fact_newsletter_contacts[opt_in_newsletter] = true()))

this throws an error:

A single value for column 'opt_in_newsletter' in table 'Fact_newsletter_contacts' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Alternatives I tried is "true" instead of true and creating it as a calculated column instead of a measure (based on some content I found online on this matter).

I'm sorry: not very tech-savvy and there's no DAX experience within our organisation...

[amitchandak]

@Spekko , is this not a column  opt_in_newsletter ?

or this a measure?

[Spekko]

@amitchandak Yes, opt_in_newsletter is a column: