Hi all,
I have the following data structure as below and I'd like to end up with a table visual which shows me the amount grouped by years in the following way:
The expected close date of Contract_ID = 1 is 2021-December and is going onwards for 12 months (Lenght in months column), so I'd like to see 100 for 2021 and 1100 for 2022 as the counting of amount starts at the month of expected close date and ends at the end of the runtime (Lenght in months column).
In case of Contract_ID = 2. The runtime is 48 months and starts in 2021 October, so:
- in 2021: 3 x 1000 (counting the monthly amount for Oct, Nov, Dec), | 45 months left
- in 2022: 12 months x 1000 | 33 months left
- in 2023: 12 months x 1000 | 21 months left
- in 2024: 12 months x 1000 | 9 months left
- and finally in 2025: 9 months x 1000
And so on, the final look would combine all the results ending up with a table of 2 columns: year and an amount.
Contract_ID | Expected Close Date | Amount_Monthly | Lenght_in_Months | Last Date |
1 | 2021.12.20 | 100 | 12 | 2021.12.01 |
| 2 | 2021.10.30 | 1000 | 48 | 2021.10.02 |
| 3 | 2021.11.29 | 100 | 37 | 2021.10.03 |
| 4 | 2021.12.20 | 1000 | 5 | 2021.12.01. |
I suppose I'm going to need a separate list of potential years as an axis and I could write some dirty measure full of IF statements, however, this is not the way I'd like to complete the task and I'm curious how you guys would approach the issue.
Thanks in advance!
