Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
Solved! Go to 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.
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.
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()
)
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
89 | |
83 | |
75 | |
49 |
User | Count |
---|---|
142 | |
140 | |
110 | |
69 | |
55 |