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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
7ballp25
Regular Visitor

CAGR HELP! Forecasting using Current data

I literally forgot how to do this, please help. 

 

I have a table that has 2018-2022 Year sales.

I created a whatifparameter from 0-100% to be the CAGR value. Focusing only on 2022 sales, I want the user to be able to select their desired CAGR % and see what the how it impacts 2023-2025 forecasted sales.

 

Forecasted Sales is dax table using Union Row...example below

 

help.PNG

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @7ballp25 
Here is a sample file with the solution https://we.tl/t-UvJsRpmiYd

Forcasted Sales = 
VAR LastAvialableYear = 
    MAX ( Data[Year] ) 
VAR LastAvailableSales = 
    CALCULATE ( SUM ( Data[Sales] ),'Data'[Year] = LastAvialableYear )
VAR CurrentYear = 
    MAX ('New Data'[Year] )
VAR YearDifference =
    CurrentYear - LastAvialableYear
VAR SelectedPercent =
    SELECTEDVALUE ( Parameter[Selected %] )
RETURN
    LastAvailableSales * POWER ( 1 + SelectedPercent, YearDifference )

1.png2.png

View solution in original post

1 REPLY 1
tamerj1
Super User
Super User

Hi @7ballp25 
Here is a sample file with the solution https://we.tl/t-UvJsRpmiYd

Forcasted Sales = 
VAR LastAvialableYear = 
    MAX ( Data[Year] ) 
VAR LastAvailableSales = 
    CALCULATE ( SUM ( Data[Sales] ),'Data'[Year] = LastAvialableYear )
VAR CurrentYear = 
    MAX ('New Data'[Year] )
VAR YearDifference =
    CurrentYear - LastAvialableYear
VAR SelectedPercent =
    SELECTEDVALUE ( Parameter[Selected %] )
RETURN
    LastAvailableSales * POWER ( 1 + SelectedPercent, YearDifference )

1.png2.png

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors