Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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....):
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 |
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
81 | |
79 | |
67 |
User | Count |
---|---|
122 | |
110 | |
94 | |
82 | |
77 |