Forum Discussion

mixue100's avatar
mixue100
Frequent Visitor
4 years ago
Solved

Calculating future months using latest actuals based on expected growth

Hi    I am stuck with this and would need help on the below.    Month YTD Actuals Expected growth for next mont, based on actuals from Current month Expected actuals (forecasted actuals) A...
Need Help
  • mangaus1111's avatar
    mangaus1111
    4 years ago

    Hi mixue100 ,

     

    this is the measure for the forecasts. Please hit the thumbs up & mark it as a solution if it helps you. Thanks.

     

    Forecasts =
    VAR LastActualMonth =
                CALCULATE (
                    MAX ( 'Table'[Month] ),
                    ALL ( 'Table' ),
                    'Table'[YTD Actuals] <> BLANK ()
                )
    VAR LastActual =
    CALCULATE (
                    SUM ( 'Table'[YTD Actuals] ),
                    ALL ( 'Table' ),
                    'Table'[Month] = LastActualMonth
                )

    VAR CurrentMonth = MAX('Table'[Month])

    VAR Product_Growth =
    CALCULATE(
     PRODUCTX(
            FILTER('Table',
                   'Table'[YTD Actuals]=BLANK() && 'Table'[Month] <= CurrentMonth
                  ),
            'Table'[Expected growth] + 1
        ),
        ALL()
    )
    RETURN
    Product_Growth * LastActual

     

     

    And here there is the link to the pbi file:

     

    https://1drv.ms/u/s!Aj45jbu0mDVJiGdkR76ccaoHdFht?e=rAmtHb

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
4 years ago

Hi mixue100 

 

If the expected growth rate is a fixed value, this is possible. You can try the following measure. For example, the growth rate is 10%. 

 

If the growth rate is not fixed, just like your sample, I cannot think of a solution yet. The difficulty is at the highlighted _forecast part. The difficulty is that in DAX, the measure is evaluated on every row individually. It cannot get the measure value from its previous row. 

 

Best Regards,
Community Support Team _ Jing