cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PDG_VL
Regular Visitor

Calculate yearly sum of values based on start date and duration lenght in month

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 DateAmount_MonthlyLenght_in_MonthsLast Date

1

2021.12.20100122021.12.01
22021.10.301000482021.10.02
32021.11.29100372021.10.03
42021.12.20100052021.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!

 

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@PDG_VL 

pls see the attachment below.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
ryan_mayu
Super User
Super User

@PDG_VL 

pls see the attachment below.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




That's great! Thanks for the quick help.

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10

The Power BI Community Show

Join us on October 3 at 11 am PST when Amit Chandak, a Power BI Super User, will demo how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors