Hey All,
I am trying to create the following measures in an ARR Bridge tab for some complex Financial Analysis:
1. Opening - opening ARR in the period i.e. for FY22 - Jun-21 ARR, FY23 - May-22 ARR and LTM Nov-23 - Nov-22 ARR.
2. Lost Customer - Opening ARR for a customer that no longer generates revenue in a given period.
3. Downsell - the difference between the opening and closing ARR when a customer generates less revenue in a given period.
4. Gross Retention - Opening less Lost Customers Less Downsell.
5. Upsell - the difference between the opening and closing ARR when a customer generates more revenue in a given period.
6. Net Retention - Gross Retention plus Upsell.
7. New customer - Closing ARR for a customer that has generated revenue for the first time in a given period.
8. Closing ARR - Net Retention plus New Customer.
All of these have to be presented for FY 22, FY 23 and LTM 22-23
Now I have a Revenue table that has Customer, Date, Revenue, ARR and some other fields. It also has a Fiscal Quarter and Year field derived from the date field.
This is the table:
| id | Customer | invoiceFrequency | inititalStartDate | currentContractLength | renewalDate | Ent | MS | recurringNRR | Product | productNumber | Month | Cost | createdAt | updatedAt |
| 63 | Abs | Annual | Nov-23 | 36 | Nov-26 | S | MS | Recurring | IR | 4 | Jun-21 | 0 | 2024-01-19 10:33:26.395 +0000 | 2024-01-19 10:33:26.395 +0000 |
| 64 | gdfb | Annual | Nov-23 | 36 | Nov-26 | S | MS | Recurring | IR | 4 | Jul-21 | 0 | 2024-01-19 10:33:26.395 +0000 | 2024-01-19 10:33:26.395 +0000 |
| 65 | gfbf | Annual | Nov-23 | 36 | Nov-26 | S | MS | Recurring | IR | 4 | Aug-21 | 0 | 2024-01-19 10:33:26.395 +0000 | 2024-01-19 10:33:26.395 +0000 |
| 66 | hfhg | Annual | Nov-23 | 36 | Nov-26 | S | MS | Recurring | IR | 4 | Sep-21 | 0 | 2024-01-19 10:33:26.395 +0000 | 2024-01-19 10:33:26.395 +0000 |
| 67 | gdgd | Annual | Nov-23 | 36 | Nov-26 | S | MS | Recurring | IR | 4 | Oct-21 | 0 | 2024-01-19 10:33:26.395 +0000 | 2024-01-19 10:33:26.395 +0000 |
| 68 | fgfgf | Annual | Nov-23 | 36 | Nov-26 | S | MS | Recurring | IR | 4 | Nov-21 | 0 | 2024-01-19 10:33:26.395 +0000 | 2024-01-19 10:33:26.395 +0000 |
How can I calculate the above measure in Dax? Are they too complex for PowerBI and should be taken to a SQL Stored procedure or can they possibly be achieved in PowerBI. Here is what the Table should look like:
| ARR Bridge | | | |
| £'000 | FY22* | FY23 | LTM Nov-23 |
| ARR | | | |
| Opening (1) | 2,755 | 5,569 | 9,013 |
| Lost Customer (2) | (117) | (278) | (896) |
| Downsell (3) | (302) | (375) | (262) |
| Gross Retention (4) | 2,336 | 4,917 | 7,855 |
| Upsell (5) | 287 | 3,340 | 1,790 |
| Net Retention (6) | 2,623 | 8,257 | 9,645 |
| New Customer (7) | 2,946 | 4,243 | 3,985 |
| Closing (8) | 5,569 | 12,500 | 13,630 |
Please guide on what should be the approach to solve this. Thanks
