Forum Discussion
data modeling
Hi everyone
I'm facing a big difficulty in data modeling. Wish someone could help me.
Let's say, in Table 1 below, SOP refers to the start date of production, life cycle refers to the number of years in which profits start, and revenue is the annual income that the project will earn in subsequent years
Table1:
| SOP | Life Cycle | Revenue |
| 2024/5 | 4 | 100 |
| 2022/5 | 3 | 200 |
| 2025/5 | 4 | 300 |
| 2023/5 | 5 | 400 |
What I want to achieve is to calculate the total annual revenue for the next ten years, as shown in Table 2, and finally show the stacked bar chart
Table 2:
| 2022 | 2023 | 2024 | 2025 | 2026 | 2027 | 2028 | 2029 | 2030 | 2031 | |
| Annual Revenue | 200 | 600 | 700 | 800 | 800 | 800 | 300 |
Any suggestion is appreciated!
Nelson
Hi, SikY
You can try the following methods.
Table:
Table 2 = var _a = GENERATESERIES(year(MIN('Table 1'[SOP])),year(MAX('Table 1'[SOP]))+MAX('Table 1'[Life Cycle])) var _b = CROSSJOIN(_a,'Table 1') return FILTER(_b,[Value]>=[SOP].[Year]&&[Value]<=[SOP].[Year]+[Life Cycle]-1)Measure:
Annual Revenue = CALCULATE(SUM('Table 2'[Revenue]),FILTER(ALL('Table 2'),[Value]=SELECTEDVALUE('Table 2'[Value])))Is this the output you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
SikY , First create a new column , in table 1
year = left([SOP],4) *1then a new table
filter(generate(Table1, generateseries(Min(Table[Life Cycle]), max(Table[Life Cycle]),1)), [Value]>= [year] && [Value] <= [Year] + [Life Cycle])
Hi, SikY
You can try the following methods.
Table:
Table 2 = var _a = GENERATESERIES(year(MIN('Table 1'[SOP])),year(MAX('Table 1'[SOP]))+MAX('Table 1'[Life Cycle])) var _b = CROSSJOIN(_a,'Table 1') return FILTER(_b,[Value]>=[SOP].[Year]&&[Value]<=[SOP].[Year]+[Life Cycle]-1)Measure:
Annual Revenue = CALCULATE(SUM('Table 2'[Revenue]),FILTER(ALL('Table 2'),[Value]=SELECTEDVALUE('Table 2'[Value])))Is this the output you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks! It works like a charm!
