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.
