Forum Discussion

davidibarrag's avatar
davidibarrag
Helper I
2 years ago

Using the last % Change as a forecast

Hello I have been strugling with this problem. I have the cummulative sales for 2 years, 2023 and 2024, and what I want to do is to forecast the next months of 2024 based on the % change of the last ...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi davidibarrag ,

You can try measures like below:

Monthly % Change =
VAR CurrentYearSales =
    CALCULATE (
        SUM ( Sales[CumulativeSales] ),
        FILTER (
            ( sales ),
            Sales[Year] = 2024
                && Sales[month] = SELECTEDVALUE ( Sales[month] )
        )
    )
VAR PreviousYearSales =
    CALCULATE (
        SUM ( Sales[CumulativeSales] ),
        FILTER (
            ( sales ),
            Sales[Year] = 2023
                && Sales[month] = SELECTEDVALUE ( Sales[month] )
        )
    )
RETURN
    DIVIDE ( CurrentYearSales - PreviousYearSales, PreviousYearSales )

Forecasted Sales =
VAR PercentageChange = selectedvalue[Monthly % Change]
VAR BaseSales =
    CALCULATE (
        SUM ( Sales[SalesAmount] ),
        FILTER ( ( sales ), Sales[Year] = 2023 )
    )
RETURN
    BaseSales * ( 1 + PercentageChange )

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

 

Best Regards,

Ada Wang

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