data modeling

[SikY]

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

[v-zhangti · Accepted solution]

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.

[SikY]

Thanks! It works like a charm!

[amitchandak]

@SikY , First create a new column , in table 1
year = left([SOP],4) *1

then a new table  

filter(generate(Table1, generateseries(Min(Table[Life Cycle]), max(Table[Life Cycle]),1)), [Value]>= [year] && [Value] <= [Year] + [Life Cycle])