Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
SamadKhan
Frequent Visitor

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.

CompanyYearRevenure
A201765
A201869
A201976
A202073
A202178
A202282
A202394
A2024108
A2025125
A2026143
A2027165
A2028190
A2029218
A2030251
B201738
B201845
B201950
B202047
B202156
B202262
B202372
B202482
B202595
B2026109
B2027125
B2028144
B2029166
B2030190
C201710
C201810
C201911
C202011
C202112
C202213
C202315
C202417
C202519
C202622
C202726
C202830
C202934
C203039

 

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?

 20172018
Company RevenueForecast RevenueForecast 
A    
B    
C    
1 ACCEPTED SOLUTION
v-yohua-msft
Community Support
Community Support

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])

 

vyohuamsft_0-1725603222927.png

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:

vyohuamsft_2-1725603578648.png

 

Here is my preview:

vyohuamsft_1-1725603303600.png

 

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.

View solution in original post

3 REPLIES 3
v-yohua-msft
Community Support
Community Support

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])

 

vyohuamsft_0-1725603222927.png

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:

vyohuamsft_2-1725603578648.png

 

Here is my preview:

vyohuamsft_1-1725603303600.png

 

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
Super User
Super User

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()
)

 


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

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.

 

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.