Calculate revenue for active subscriptions with a start and end date

[Anonymous]

Hi,

I have a number of active subscriptions that are active on an adreskey between a start and end date.
As I have a date table as well, I can now plot the number of active subscriptions per date.
The date table is just a CALENDARAUTO generated table, with a month name, month nr, and year column added.

As a third table I have the monthly price of the subscription.

None of the 3 tables have a relationship in the data model.
I would like to see my total revenue per subscription per month in a matrix.

I use this measure to calculate the number of active subscriptions. This I can plot on a date (but not a month....):

Active Sub =

CALCULATE(

SUM('Active Sub'[Number]),

FILTER('Active Sub',

AND('Active Sub'[Start date]<=SELECTEDVALUE(Dates[Date]),

'Active Sub'[End date]>=SELECTEDVALUE(Dates[Date]))))

I get stuck on the next step to calculate the revenue per month (number of active subscriptions * the month price)...

And then plot this in a matrix with the subsriptions in the rows and the months (or even better a date hierarchy) in the columns.

Who can help me please?

KR
Analyst J

My active subscription table:

Adreskey	Sub	Start date	End date	Number
A	1	1-1-2020	31-12-2020	1
B	1	5-1-2020	1-12-2020	1
C	2	1-2-2020	1-7-2020	1
D	3	1-3-2020	1-4-2020	1
E	4	1-4-2020	1-8-2020	1
F	5	1-5-2020	1-10-2020	1
G	5	1-2-2020	1-11-2020	1
H	6	1-6-2020	15-6-2020	1
I	7	1-7-2020	1-11-2020	1
J	8	1-8-2020	1-9-2020	1
K	9	1-9-2020	31-12-2020	1
L	10	1-10-2020	31-12-2020	1

 

 My subscription prices table:

Sub	Sub name	jan-20	feb-20	mrt-20	apr-20	mei-20	jun-20	jul-20	aug-20	sep-20	okt-20	nov-20	dec-20
1	Subscription 1	10	10	10	10	10	10	11	11	11	11	11	11
2	Subscription 2	20	20	20	20	20	20	20	20	20	20	20	20
3	Subscription 3	30	30	25	25	25	25	25	30	30	30	30	30
4	Subscription 4	40	40	40	40	40	40	50	50	50	50	50	50
5	Subscription 5	50	50	50	50	50	50	55	55	55	55	55	55
6	Subscription 6	60	60	60	60	60	60	60	60	60	60	60	60
7	Subscription 7	70	70	70	75	75	75	75	75	75	75	75	75
8	Subscription 8	80	80	80	80	80	80	80	80	80	80	80	80
9	Subscription 9	90	90	85	85	85	90	90	90	95	95	95	95
10	Subscription 10	100	100	100	100	100	100	100	100	100	100	100	100

[v-stephen-msft]

Hi @Anonymous ,

Sorry for my late repley.

I am coufused about your subscription prices table. Could you please give an example to show how to calculate revenue by you subscription prices table? And what kind of result you want, it is best to present it in the form of screenshots.

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.

[Anonymous]

Any thoughts from the experts here, maybe?
I could really use some help or pointers on how to approach this.

Thanks in advance!
 @amitchandak , @mahoneypat @Fowmy @PhilipTreacy