Hi, I have a dataset of all contracts of a company over time. Some contracts are already expired, some will expire in the coming years. What I want is to show a monthly chart with current active contracts and the monthly reduction.
Below are some fake examples of the dataset. I can't attach it here because it's confidential.
| Customer ID | Contract Number | Contract start | Contract end | Status |
| 1 | 100000 | 01/01/2019 | 01/01/2021 | Activated |
| 1 | 100005 | 01/01/2015 | 31/12/2018 | Terminated |
| 2 | 100020 | 15/06/2020 | 31/12/2025 | Activated |
| 3 | 100150 | 25/04/2018 | 24/04/2023 | Activated |
| 3 | 100033 | 25/01/2016 | 24/04/2018 | Terminated |
| 4 | 100200 | 19/01/2017 | 18/01/2027 | Activated |
| 5 | 100500 | 01/01/2020 | 31/12/2022 | Activated |
So this month the chart would show all active contracts, next month it would show all active contracts minus the contracts expiring that month and so on.
I made a chart with the [Contract end] as dimension and I was able to get the contracts expiring each month with:
COUNT({$<[Contract end]={'>=$(=today())'}, Status = {"Activated"}>} DISTINCT[Contract Number])
Also, in a textbox I can get the total active contracts right now:
SUM(COUNT({$<[Contract end]={'>=$(=today())'}, Status={"Activated"}>} TOTAL DISTINCT [Contract Number]))
However, I don't know how to combine these two. I can't use back accumulation because contracts have different durations, so having a fixed backstep wouldn't do the trick.
Note: the filter where Contract end >= today is important because sometimes the database is not properly updated and the contract is shown as activated even if the date is already in the past.
