If events are happening at the same time, get the number of events

[Anonymous]

Hey guys,

I have the following problem. I have a huge table with millions of customers who have different kind of subscriptions (there are like 50+ different subscription types). And what i wanna know, is how many subscriptions they have at the same time at a certain time.

For example this:

Customer	Subscription	Start	End
11	a	1-1-2018	4-8-2018
22	b	1-1-2019	1-1-2020
33	c	1-1-2018	1-1-2020
44	d	1-1-2018	1-1-2020
11	e	1-1-2019	1-1-2020
22	f	1-4-2025	1-5-2018
44	g	1-1-2018	4-8-2018
44	h	1-1-2019	1-1-2020
44	i	1-1-2018	1-1-2020
44	j	1-1-2018	1-1-2020
44	k	1-1-2019	1-1-2020
44	l	1-4-2025	1-5-2018

And what i would like to know is:

Per customer, per month, number of subscriptions at the same time. 

Customer	Month	N at the same time
11	january 2018	1
22	january 2018	1
33	january 2018	1
44	january 2018	3

Any ideas? I am stuck at the execution part completely. >_<

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Your data is confusing by some start date> End date. I modified some of them to keep the Start date<End date. then processing the data:

1. Create a calendar table:

Table 2 = CALENDAR(DATE(2018,1,1),DATE(2020,5,1))

2. Add below measure:

Measure = CALCULATE(DISTINCTCOUNT('Table'[Subscription]),FILTER(ALL('Table'),MAX('Table 2'[Date])>=[Start]&&MIN('Table 2'[Date])<=[End]),VALUES('Table'[Customer]))

You'll get the expected results:

Pbix attached.

[Anonymous]

Insert a Matrix visual with row: customer, column: month, value: subscription count.

[Anonymous]

Doesn't work like that, they can be both in the same month, but not at the same time (for example one ends on 10th, and the other starts on the 11th).

[Anonymous]

So you actually need it of a day to day basis?

I may have skipped a few steps, because wheri i mention 'month' you may need to add that trough power query:

Go to edit queries > add column > custom column and use this formula including all brackets:

={Number.From([Start])..Number.From([End])}
Then in the new columnheader click the arrows pointing both ways, and click expand to new rows.

You now have the same table you started with, buth with a single row for each day between start and end.

You should be able to work with that

(before you replied: You do not need each day, so do the following:

Click the new column and click the transform tab > date > month > Start on month.

Now we have a lot of duplicates, al with the first day of the month.

Select ALL of your columns, right click on one and select remove duplicates.

Now rename you 'Custom' column to month. /orginal answer)