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 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 Date | Amount_Monthly | Lenght_in_Months | Last Date |
1 | 2021.12.20 | 100 | 12 | 2021.12.01 |
2 | 2021.10.30 | 1000 | 48 | 2021.10.02 |
3 | 2021.11.29 | 100 | 37 | 2021.10.03 |
4 | 2021.12.20 | 1000 | 5 | 2021.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!
Solved! Go to Solution.
pls see the attachment below.
Proud to be a Super User!
pls see the attachment below.
Proud to be a Super User!
That's great! Thanks for the quick help.
you are welcome
Proud to be a Super User!
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |