Hi all - I need some help thinking through my current situation and the best way to structure/link my data within PowerBi so it will be reliable for many years down the road (assuming software changes don't break things).
Data Example:
| Contract Name | Value | Start Date | End Date | First Year Value | Second Year | (Final year determined by latest end date) |
| Name1 | $500 | 11/24/2020 | 01/01/2021 | auto fill | auto fill | auto fill |
| Name2 | $1,000 | 01/01/2021 | 01/01/2022 | auto fill | auto fill | auto fill |
| Name3 | $500,000 | 05/20/2022 | 01/20/2030 | autofill | auto fill | auto fill |
Overview of what we are trying to do:
Right now we have a very disorganized spreadsheet that takes the Value of a Contract and splits the value into a year by year record based on the contract Start Date & End Date This allows us to see how much we expect to bring in year over year and get an outlook for the next 6+ years.
Now, this is where things get kind of tricky:
Name3 Example:
We can't just do $500k / (# of months between start/end date) because the first year, 2022, only has 8 months (including 5/2022), and 2023 has 12 full months of $.
We have over 400 contracts and some are over 10 years so the process needs to be structured in a way where we can easily filter by year.
I am thinking the best way for this would be to use a DAX formula that I can take the contract name, divide value by # of months (length of contract), and then get a year over year total (this part trips me up because I don't know how I can figure out if there are 12 months worth of $ or just 8, ect.)
Once I get this figure for Year x $, Year Y $, ect, I am also struggling to figure out the best way to display that in a table so I can have it displayed correctly in a visual when filters are applies.
Hopefully that explanation made some sense. I've been trying to wrap my head around the best way to approach this and structure everything over the last few days. I would greatly appreciate any and all suggestions some of your experience users may have.
