Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@RVLee , refer if the file / blog to deal with date ranges can help you
File attached after signature
@RVLee , refer if the file / blog to deal with date ranges can help you
File attached after signature
Hi @amitchandak,
Awesome! It gives me an idea on how to generate the years through certain periods. Thanks a lot!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |