Recently, I came across an interesting business problem for a SaaS company and I packed it nicely for you to wrack your brains on it.
Quick Note – What is a SaaS Business?
A company that creates software / digital products and customers pay a monthly rent to use the software is commonly known as SaaS. (Software as a Service)
If you prefer watching a Video!
Dataset
It’s a really simple dataset where a row means a single transaction (monthly payment received) and the columns mean the following.
- Transaction Date – Dates when the customers were billed each month. Each customer only pays once per product per month
- Transaction ID – Unique transaction ID
- User Email – Customer’s Email
- User ID – Unique Customer ID
- Product – The product the customer is subscribed to
- Version – Each product comes with 2 versions, Base Version and Professional Version
The Challenge
Find the number of upgrades that happen each month.
- An Upgrade is when the user moves to the Professional version of the Product from the Base Version in the next consecutive month.
- For eg the user id – 22761639 made an upgrade in Aug 2019, that’ll be counted as 1 upgrade for Aug.
The Output
To be really sure that you have the correct answer, your numbers should match with mine below.
The Rules
- Use Power BI to solve this.
- Feel free to add a Date table should you need it.
- I don’t mind, if you work out the solution in Power Query or by creating a few helper columns using DAX.
- Ideally I’d give bonus points if you don’t change the data structure and knock this off with a single measure.
A bit shout out to everyone who comments a correct answer and bonus shout out to nifty solutions! Leave a comment with the snapshot of your answer and a link to download your pbix file!
Get to work boys/girls!
Solution Video
More DAX Puzzles
- SUMX Problem
- Growth from Previous Non-Consecutive Date
- Calculate New Subscribers Added
- DAX Crossword
- How much is the Accrued Commission
