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

Need Help
Tips and Tricks

1 Reply

  • 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