Hi,
I need some ideas to get future trend value.
I have a table called Contract, and it has records with the contract start date and end date for each day, only until current year, which is before the year of contract end date, or at the year if the current year is the end date.
For a clearer picture, here's the example of the table.
| Contract Name | Version | Date | Contract Start Date | Contract End Date | Value |
| Contract A | 1 | 1/1/2021 | 1/1/2021 | 12/31/2023 | 100 |
| Contract A | 1 | 1/2/2021 | 1/1/2021 | 12/31/2023 | 100 |
| (Contract A rows exist until Date = today) | | | |
| Contract B | 1 | 3/1/2021 | 3/1/2021 | 2/28/2022 | 500 |
| Contract B | 1 | 3/2/2021 | 3/1/2021 | 2/28/2022 | 500 |
| (Contract B rows exist until Date = Contract End Date for version 1) | |
| Contract B | 2 | 3/1/2022 | 3/1/2022 | 2/28/2024 | 490 |
| Contract B | 2 | 3/2/2022 | 3/1/2022 | 2/28/2024 | 490 |
| (Contract B rows exist until Date = Contract End Date for version 2) | |
| Contract C | 1 | 5/1/2021 | 5/1/2021 | 5/1/2022 | 300 |
| Contract C | 1 | 5/2/2021 | 5/1/2021 | 5/1/2022 | 300 |
And the result I'm trying to create is below - which I can use to put in the line graph for future trend of contract value.
| Contract Name | Year | Value | (Notes on calculation) |
| Contract A | 2022 | 100 | |
| Contract A | 2023 | 100 | |
| Contract B | 2022 | 490 | Retrieving Contract Value at the end of year |
| Contract B | 2023 | 490 | |
| Contract B | 2024 | 490 | |
| Contract C | 2022 | 300 | Include the active contracts in the year although it ended before end of year |
I have a Calendar table that has one-to-many relationship with the Date column of the Contract table.
I'm open with any way, either creating a table like the result table above, or a measure calculation.
Looking forward.
