Using the last % Change as a forecast

[davidibarrag]

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 month in the cummulative sales. Lets say that our % change of cummulative sales of may 2024 vs 2023 is 5.5%, then I need to multiply this 5.5% for the normal sales (no cummilative sales) of june 2023, july 2023... december 2023 to forecast the months of 2024. If the % change of cummulative sales of june 2023 vs june 2024 is different than 5.5%, then I need to change the forcast for the remaining months of 2024 with the new % change (june 2023 vs june 2024). 

Thanks

[Anonymous]

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