Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am trying to create a montly MRR overview where a measure calculates the sum amount of the subscriptions which are active during that month. Preferable also incorporating half months. Sow where startdate or enddate of subscription is on the 15th of the month it is 0,5 of month. Can also be (15/daysinmonth)* Amount. This is the data.
Name | Startdate | enddate | Monthly amount |
Customer A | 1-1-2020 | 30-6-2020 | 300 |
Customer A | 1-7-2020 | 31-12-2020 | 500 |
Customer B | 1-1-2020 | 31-12-2020 | 300 |
Customer C | 1-2-2020 | 15-10-2020 | 650 |
New subscriptions are added eacht month.
I Am trying to figure something out where I create a list in the query editor from 1...12 with wich I could use as monthnum to check of a subscriptionline is active during that month, but how exactly?. Maybe it is also completely solvable in DAX.
Hope you can help me I am open to any ideas.
Solved! Go to Solution.
Hi @BobKoenen,
I'd like to suggest you to add an expand table to expand detail records of your table, then you can simply calculate the rolling total amount based on expanding the table.
Spread revenue across period based on start and end date, slice and dase this using different dates
Regards,
Xiaoxin Sheng
Hi @BobKoenen,
I'd like to suggest you to add an expand table to expand detail records of your table, then you can simply calculate the rolling total amount based on expanding the table.
Spread revenue across period based on start and end date, slice and dase this using different dates
Regards,
Xiaoxin Sheng
Thankx! it works. A bit tricky when no months are selected but none the less thanks!
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |