Hello,
I am trying to build a calculator that will return a measure depending of two inputs:
- Start date (I thought of using a date picker "After")
- Duration (1,2 or 3 years: I thought of using a What if new parameter for this)
I am using a regular calendar table and another dataset with subscription information.
The subscription dataset looks like this :
| Type of plan | End of subscription |
| A | 12/25/2021 |
| A | 1/13/2022 |
| A | 1/18/2022 |
| A | 1/23/2022 |
| B | 2/3/2022 |
| B | 4/26/2022 |
| B | 7/12/2022 |
| B | 12/25/2023 |
| A | 12/25/2024 |
| A | 12/25/2024 |
| A | 5/4/2025 |
| B | 5/13/2025 |
| A | 7/12/2025 |
The measure I want to calculate is the sum of % of subscription ongoing during the input period : starting on picked date and for the duration selected (1-2-3 years)
The subscription dataset once pivoted looks like this (number of subscription expiring in the coming years)
| | 2021 | 2022 | 2023 | 2024 |
| | Dec | Jan | Feb | Apr | Nov | Dec | May | Jul |
| A | 1 | 3 | | | | | 1 | 3 |
| B | | | 1 | 1 | 1 | 1 | 1 | |
| Grand Total | 1 | 3 | 1 | 1 | 1 | 1 | 2 | 3 |
So let's say I select 1 year from Aug 2021:
- I look at the number expiry happening in that timeframe Aug 2021 - July 2022 (row3)
- I count the number of month remaining till the end of the period (row1)
- Calculate a % (Number of month remaining / Number of month in the duration) (row4)
- multiply this % with the number of expiration (row5)
- get my measure with a sum of row 5
| Row No. | | 2021 | 2022 |
| 1 | Number of month remaining for selected duration | 8 | 7 | 6 | 4 |
| 2 | Month | Dec | Jan | Fev | Apr |
| 3 | Count of subscription A&B expiring | 1 | 3 | 1 | 1 |
| 4 | % of time during the 1 year
(Number of month remaining / 12) | 67% | 58% | 50% | 33% |
| 5 | (A+B)* % of time | 0.7 | 1.8 | 0.5 | 0.3 |
Measure = sum of (A+B)* % of time = 3
For 2 years (24 months) from Aug 2021:
| | 2021 | 2022 |
| Number of month remaining for selected duration | 20 | 19 | 18 | 16 | 9 |
| Month | Dec | Jan | Fev | Apr | Nov |
| Count of subscription A&B expiring | 1 | 3 | 1 | 1 | 1 |
% of time during the 1 year
(Number of month remaining / 24) | 83% | 79% | 75% | 67% | 38% |
| (A+B)* % of time | 0.8 | 2.4 | 0.8 | 0.7 | 0.4 |
Measure = sum of (A+B)* % of time = 5
For 3 years (36 months) from Aug 2021:
| | 2021 | 2022 | 2023 | 2024 |
| Number of month remaining for selected duration | 32 | 31 | 30 | 28 | 21 | 8 | 3 | 1 |
| Month | Dec | Jan | Feb | Apr | Nov | Dec | May | Jul |
| Count of subscription A&B expiring | 1 | 3 | 1 | 1 | 1 | 1 | 2 | 3 |
% of time during the 1 year
(Number of month remaining / 36) | 89% | 86% | 83% | 78% | 58% | 22% | 8% | 3% |
| (A+B)* % of time | 0.9 | 2.6 | 0.8 | 0.8 | 0.6 | 0.2 | 0.2 | 0.1 |
Measure = sum of (A+B)* % of time = 6
Any insights on how to proceed to automate this?
I created a file with the same dummy data here : https://we.tl/t-8DUjTVDxaq
Many thanks
