Forecast per Company distributed yearly

[SamadKhan]

Hello Experts,

I have a revenue data for each company distributed from 2017 to 2030. I want to calculate forecast from the selected year until the last year, that is 2030. Data seems like following.

Company	Year	Revenure
A	2017	65
A	2018	69
A	2019	76
A	2020	73
A	2021	78
A	2022	82
A	2023	94
A	2024	108
A	2025	125
A	2026	143
A	2027	165
A	2028	190
A	2029	218
A	2030	251
B	2017	38
B	2018	45
B	2019	50
B	2020	47
B	2021	56
B	2022	62
B	2023	72
B	2024	82
B	2025	95
B	2026	109
B	2027	125
B	2028	144
B	2029	166
B	2030	190
C	2017	10
C	2018	10
C	2019	11
C	2020	11
C	2021	12
C	2022	13
C	2023	15
C	2024	17
C	2025	19
C	2026	22
C	2027	26
C	2028	30
C	2029	34
C	2030	39

I want to have it in table as below

if is it possible if i select year 2022 and the table will show numbers from 2022 and onwards instead of 2017?

	2017	2018
Company	Revenue	Forecast	Revenue	Forecast
A
B
C

[v-yohua-msft · Accepted solution]

Hi, @SamadKhan 

You can create a new measure, try the following DAX expression:

Forecast = 
VAR LastYear = MAX('Table'[Year])
VAR GrowthRate = 0.07 -- Adjust this based on your data
RETURN
SUMX(
    FILTER(
        'Table',
        'Table'[Year] >= LastYear
    ),
    'Table'[Revenue] * (1 + GrowthRate)
)

If you want to select 2022, the table will show numbers for 2022 and beyond instead of 2017, you can create a new table

Table 2 = VALUES('Table'[Year])

Then create a new measure:

Revenure measure = 
VAR _Slicer = MAX('Table 2'[Year])
RETURN 
IF(MAX('Table'[Year])<_Slicer,BLANK(),SUM('Table'[Revenure]))

Put revenure measure and Forevast in matrix view, and put table 2 [year] in slicer view:

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sahir_Maharaj]

Hello @SamadKhan,

Can you please try the following approach:

Forecast Revenue = 
VAR SelectedYear = SELECTEDVALUE('YourTable'[Year])
RETURN
IF(
    MAX('YourTable'[Year]) >= SelectedYear,
    CALCULATE(
        SUM('YourTable'[Revenue]),
        FILTER(
            'YourTable',
            'YourTable'[Year] >= SelectedYear
        )
    ),
    BLANK()
)

[SamadKhan]

Hello @Sahir_Maharaj 

Thank you for your solution. 

I was thinking how can I calculate an increase of 7 % from a selected year. If I select 2021 as a base year and then 2022 onwards 2030 will show increase of 7%  per year on revenue of the last year. Example 2022 shows 7% increase on 2021 and 2023 shows 7% increase of 2022.

Another scenario, Is there any way if I can set % on my own except 7% and it will be calculated from the selected year as mentioned in scenario above.