Forum Discussion

RVLee's avatar
RVLee
Frequent Visitor
4 years ago
Solved

Calculating future dates trend

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 NameVersionDateContract Start DateContract End DateValue
Contract A11/1/20211/1/202112/31/2023100
Contract A11/2/20211/1/202112/31/2023100
(Contract A rows exist until Date = today)   
Contract B13/1/20213/1/20212/28/2022500
Contract B13/2/20213/1/20212/28/2022500
(Contract B rows exist until Date = Contract End Date for version 1) 
Contract B23/1/20223/1/20222/28/2024490
Contract B23/2/20223/1/20222/28/2024490
(Contract B rows exist until Date = Contract End Date for version 2) 
Contract C15/1/20215/1/20215/1/2022300
Contract C15/2/20215/1/20215/1/2022300

 

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 NameYearValue(Notes on calculation)
Contract A2022100 
Contract A2023100 
Contract B2022490Retrieving Contract Value at the end of year
Contract B2023490 
Contract B2024490 
Contract C2022300Include 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.

Need Help

2 Replies