Forum Discussion
Forecast per Company distributed yearly
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 |
Hi, Anonymous
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.
3 Replies
Hello Anonymous,
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() )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.
Hi, Anonymous
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.
